﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GAMS.MODEL;
using System.Data;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_GENERAL_Ledger
    {
        private DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        private ConvertNullable cn = new ConvertNullable();

        public IList<M_ACM_GENERAL_Ledger> GetHeaderFullData(Nullable<int> bgm_year, Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , string tran_type, string tran_code, Nullable<DateTime> tran_date_start, Nullable<DateTime> tran_date_end)
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@tran_type", tran_type);
                dbManager.AddParameters(5, "@tran_code", tran_code);
                dbManager.AddParameters(6, "@dtStart", tran_date_start);
                dbManager.AddParameters(7, "@dtEnd", tran_date_end);

                string strSQL = "SELECT t.bgm_year, t.tran_code, t.tran_seq, t.tran_type, t.work_area_seq, t.ref_no"
                    + ", t.bgm_source_seq, t.tran_date, t.tran_name, t.remarks, t.record_status,td.amount"
                    + ", case t.record_status"
                        + " when 'A' then 'Posted'"
                        + " else 'รอ Post'"
                        + " end as record_status_name"
                    + " FROM acm_transection t"
                    + " left join (select tran_seq, sum(coalesce(amount, 0)) as amount"
                        + " from acm_transection_dtls"
                        + " where dr_or_cr = 'Dr'"
                        + " and record_status not like @delete_status"
                        + " group by tran_seq"
                        + ") td on t.tran_seq = td.tran_seq"
                    + " WHERE t.record_status not like @delete_status"
                    + (bgm_year != null ? " and t.bgm_year = @bgm_year" : "")
                    + (bgm_source_seq != null ? " and t.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " and t.work_area_seq = @work_area_seq" : "")
                    + (!string.IsNullOrEmpty(tran_type) ? " and t.tran_type = @tran_type" : "")
                    + (!string.IsNullOrEmpty(tran_code) ? " and t.tran_code like '%" + tran_code + "%'" : "")
                    + (tran_date_start != null ? " and t.tran_date >= @dtStart" : "")
                    + (tran_date_end != null ? " and t.tran_date <= @dtEnd" : "")
                    + " ORDER BY t.tran_date, t.tran_seq";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.tran_code = drc[i]["tran_code"].ToString();
                        info.tran_seq = Convert.ToInt32(drc[i]["tran_seq"]);
                        info.tran_type = drc[i]["tran_type"].ToString();
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.tran_date = cn.ConvertNullDateTime(drc[i]["tran_date"]);
                        info.tran_name = drc[i]["tran_name"].ToString();
                        info.ref_no = drc[i]["tran_type"].ToString() == "A" ? drc[i]["tran_code"].ToString() : drc[i]["ref_no"].ToString();
                        info.remark = drc[i]["remarks"].ToString();
                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public M_ACM_GENERAL_Ledger GetHeaderInfo(int tran_seq)
        {
            DataRowCollection drc = null;
            M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@tran_seq", tran_seq);

                string strSQL = "SELECT bgm_year, tran_code, tran_seq, tran_type, work_area_seq"
                    + ", bgm_source_seq, tran_date, tran_name, remarks, record_status"
                    + ", case record_status"
                        + " when 'A' then 'Posted'"
                        + " else 'รอ Post'"
                        + " end as record_status_name"
                    + " FROM acm_transection"
                    + " WHERE tran_seq = @tran_seq";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.tran_seq = Convert.ToInt32(drc[0]["tran_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.tran_code = drc[0]["tran_code"].ToString();
                    info.tran_type = drc[0]["tran_type"].ToString();
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.tran_date = cn.ConvertNullDateTime(drc[0]["tran_date"]);
                    info.tran_name = drc[0]["tran_name"].ToString();
                    info.remark = drc[0]["remarks"].ToString();
                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return info;
        }

        public IList<M_ACM_GENERAL_Ledger> GetDtlsFullData(Nullable<int> bgm_year, Nullable<int> bgm_source_seq
            , Nullable<int> work_area_seq, string tran_type, string tran_code, Nullable<DateTime> dtStart, Nullable<DateTime> dtEnd
            , Nullable<int> tran_seq)
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();
                string strSQL = "";
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@tran_type", tran_type);
                dbManager.AddParameters(5, "@tran_code", tran_code);
                dbManager.AddParameters(6, "@dtStart", dtStart);
                dbManager.AddParameters(7, "@dtEnd", dtEnd);
                dbManager.AddParameters(8, "@tran_seq", tran_seq);

                strSQL = "SELECT td.tran_dtls_seq, td.tran_seq" //, t.fiscal_period, t.tran_grp_seq"
                    //+ ", t.tran_code, t.tran_type, t.tran_date, t.tran_name, t.bgm_year, t.work_area_seq, t.bgm_source_seq"
                    + ", td.account_seq, td.dr_or_cr, acc.account_code, acc.account_name"
                    + ", case td.dr_or_cr"
                        + " when 'Dr' then td.amount"
                        + " when 'Cr' then null"
                        + " end as debit_amount"
                    + ", case td.dr_or_cr"
                        + " when 'Dr' then null"
                        + " when 'Cr' then td.amount"
                        + " end as credit_amount"
                    + " FROM acm_transection_dtls td"
                    //+ " Left Join acm_transection_dtls td ON t.tran_seq = td.tran_seq and td.record_status not like @delete_status"
                    + " Left Join acm_mas_account acc ON td.account_seq = acc.account_seq"
                    + " WHERE td.record_status not like @delete_status"
                    //+ (bgm_year != null ? " and t.bgm_year = @bgm_year" : "")
                    //+ (bgm_source_seq != null ? " and t.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (work_area_seq != null ? " and t.work_area_seq = @work_area_seq" : "")
                    //+ (!string.IsNullOrEmpty(tran_type) ? " and t.tran_type = @tran_type" : "")
                    //+ (!string.IsNullOrEmpty(tran_code) ? " and t.tran_code like '%" + tran_code + "%'" : "")
                    //+ (dtStart != null ? " and t.tran_date >= @dtStart" : "")
                    //+ (dtEnd != null ? " and t.tran_date <= @dtEnd" : "")
                    + (tran_seq != null ? " and td.tran_seq = @tran_seq" : "")

                    + " ORDER BY td.tran_dtls_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.tran_dtls_seq = cn.ConvertNullInt(drc[i]["tran_dtls_seq"]);
                        info.tran_seq = Convert.ToInt32(drc[i]["tran_seq"]);
                        //info.tran_type = drc[i]["tran_type"].ToString();
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        //info.tran_code = drc[i]["tran_code"].ToString();
                        //info.tran_date = cn.ConvertNullDateTime(drc[i]["tran_date"]);
                        //info.tran_name = drc[i]["tran_name"].ToString();
                        info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
                        info.debit_amount = cn.ConvertNullDouble(drc[i]["debit_amount"]);
                        info.credit_amount = cn.ConvertNullDouble(drc[i]["credit_amount"]);
                        //info.bgm_source_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        //info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        //info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        //info.fiscal_period = drc[i]["fiscal_period"].ToString();
                        //info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public void Post(int tran_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@tran_seq", tran_seq);

                strSQL = "UPDATE acm_transection"
                    + " SET record_status = 'A'"
                    + " WHERE tran_seq = @tran_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetNextTranGrpSeq()
        {
            DataTable dtb = null;
            int nNextTranGrpSeq = 1;
            try
            {
                dbManager.Open();

                string strSQL = "SELECT max(coalesce(tran_grp_seq, 0)) as tran_grp_seq"
                    + " from acm_transection"
                    + " where record_status not like 'D'";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    if (dtb.Rows[0]["tran_grp_seq"] != DBNull.Value)
                    {
                        nNextTranGrpSeq = Convert.ToInt32(dtb.Rows[0]["tran_grp_seq"]) + 1;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return nNextTranGrpSeq;
        }

        public string GetNextTranCode(string bgm_source_seq, string bgm_year, string tran_type)
        {
            DataTable dtb = null;
            string strNextTranCode = tran_type + "-" + (!string.IsNullOrEmpty(bgm_source_seq) ? (bgm_source_seq == "1" ? "1" : "2") : "3") + "-" + bgm_year + "-00001";
            try
            {
                dbManager.Open();

                string strSQL = "SELECT max(CONVERT(VARCHAR(50),tran_code)) as tran_code"
                    + " from acm_transection"
                    + " where record_status not like 'D'"
                    + " and bgm_source_seq = " + (!string.IsNullOrEmpty(bgm_source_seq) ? bgm_source_seq : "1")
                    + " and bgm_year = " + bgm_year
                    + " and tran_type = '" + tran_type + "'"

                    + " group by bgm_source_seq, bgm_year, tran_type";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    if (dtb.Rows[0]["tran_code"] != DBNull.Value)
                    {
                        strNextTranCode = tran_type + "-" + (!string.IsNullOrEmpty(bgm_source_seq) ? (bgm_source_seq == "1" ? "1" : "2") : "3") + "-" + bgm_year + "-" + (Convert.ToInt32(dtb.Rows[0]["tran_code"].ToString().Split('-')[3]) + 1).ToString("00000");
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return strNextTranCode;
        }

        public IList<M_ACM_GENERAL_Ledger> GetListFillByStatusAccount(string pStatus, string pTran_Type)
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();
                string strSQL = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@tran_type", pTran_Type);

                if (pStatus == "รอการอนุมัติ")
                {
                    strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                               + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                               + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                               + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                               + " ,acc_dtls.record_status ,acc_dtls.dr_or_cr "
                               + " ,acc_tran.created_by,acc_tran.created_when"
                               + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                               + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount ,ref_type "
                               + " FROM acm_transection AS acc_tran "
                               + " Left Join acm_transection_dtls AS acc_dtls  ON acc_tran.tran_seq = "
                               + " acc_dtls.tran_seq and acc_dtls.record_status not like 'D' "
                               + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                               + " Left Join acm_transection_group ON acm_transection_group.tran_grp_seq = acc_tran.tran_grp_seq "
                               + " WHERE acc_tran.record_status not like  'D' "
                               + " AND approved_when is null "
                               + " AND acc_tran.tran_type = @tran_type "
                               + " ORDER BY acc_tran.tran_seq DESC,acc_dtls.tran_dtls_seq ";
                }
                else if (pStatus == "อนุมัติ")
                {
                    strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                                  + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                                  + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                                  + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                                  + " ,acc_dtls.record_status ,acc_dtls.dr_or_cr "
                                  + " ,acc_tran.created_by,acc_tran.created_when"
                                  + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                                  + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount ,ref_type "
                                  + " FROM acm_transection AS acc_tran "
                                  + " Left Join acm_transection_dtls AS acc_dtls  ON acc_tran.tran_seq = "
                                  + " acc_dtls.tran_seq and acc_dtls.record_status not like 'D' "
                                  + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                                  + " Left Join acm_transection_group ON acm_transection_group.tran_grp_seq = acc_tran.tran_grp_seq "
                                  + " WHERE acc_tran.record_status not like  'D' "
                                  + " AND approved_when is not null "
                                  + " AND acc_tran.tran_type = @tran_type "
                                  + " ORDER BY acc_tran.tran_seq DESC ";
                }
                else if (pStatus == "ยกเลิก")
                {
                    strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                              + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                              + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                              + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                              + " ,acc_dtls.record_status ,acc_dtls.dr_or_cr "
                              + " ,acc_tran.created_by,acc_tran.created_when"
                              + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                              + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount ,ref_type "
                              + " FROM acm_transection AS acc_tran "
                              + " Left Join acm_transection_dtls AS acc_dtls  ON acc_tran.tran_seq = "
                              + " acc_dtls.tran_seq  "
                              + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                              + " Left Join acm_transection_group ON acm_transection_group.tran_grp_seq = acc_tran.tran_grp_seq "
                              + " WHERE acc_dtls.record_status =  'D' and acc_tran.record_status not like  'D'"
                              + " AND acc_tran.tran_type = @tran_type "
                              + " ORDER BY acc_tran.tran_seq DESC ";
                }
                else if (pStatus == "ทั้งหมด")
                {
                    strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                               + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                               + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                               + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                               + " ,acc_dtls.record_status ,acc_dtls.dr_or_cr "
                               + " ,acc_tran.created_by,acc_tran.created_when"
                               + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                               + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount ,ref_type "
                               + " FROM acm_transection AS acc_tran "
                               + " Left Join acm_transection_dtls AS acc_dtls  ON acc_tran.tran_seq = "
                               + " acc_dtls.tran_seq  "
                               + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                               + " Left Join acm_transection_group ON acm_transection_group.tran_grp_seq = acc_tran.tran_grp_seq "
                               + " WHERE acc_tran.record_status not like  'D' "
                               + " AND acc_tran.tran_type = @tran_type "
                               + " ORDER BY acc_tran.tran_seq DESC ";
                    //+ " WHERE acc_tran.record_status not like  'D' "
                }

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.tran_dtls_seq = cn.ConvertNullInt(drc[i]["tran_dtls_seq"]);
                        info.tran_seq = Convert.ToInt32(drc[i]["tran_seq"]);
                        info.ref_type = drc[i]["ref_type"].ToString();
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        info.account_name = drc[i]["acc_name"].ToString();
                        info.tran_code = drc[i]["tran_code"].ToString();
                        info.tran_date = cn.ConvertNullDateTime(drc[i]["tran_date"]);
                        info.tran_name = drc[i]["tran_name"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
                        info.debit_amount = cn.ConvertNullDouble(drc[i]["dtls_debit_amount"]);
                        info.credit_amount = cn.ConvertNullDouble(drc[i]["dtls_credit_amount"]);
                        info.created_by = Convert.ToInt32(drc[i]["created_by"]);
                        info.created_when = Convert.ToDateTime(drc[i]["created_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERAL_Ledger> GetListTocmbSourceMoney()
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();

                string strSQL = " SELECT s.bgm_source_seq, s.bgm_source_name, s.upper_bgm_source_seq "
                                + " , us.bgm_source_name as upper_bgm_source_name, s.record_status "
                                + " FROM bgm_mas_source s "
                                + " LEFT JOIN bgm_mas_source us ON s.upper_bgm_source_seq = us.bgm_source_seq "
                                + " LEFT JOIN bgm_mas_source ls ON s.bgm_source_seq = ls.upper_bgm_source_seq "
                                + " AND ls.record_status not like 'D' "
                                + " WHERE s.record_status not like 'D' "
                                + " and ls.bgm_source_seq is null "
                                + " ORDER BY s.bgm_source_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERAL_Ledger> GetListToGrdDebit_Credit(M_ACM_GENERAL_Ledger infoparam)
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@tran_seq", infoparam.tran_seq);
                dbManager.AddParameters(1, "@dr_or_cr", infoparam.dr_or_cr);
                string strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_dtls.tran_seq,acc_dtls.account_seq,acc_dtls.amount "
                               + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                               + " FROM acm_transection_dtls AS acc_dtls "
                               + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                               + " WHERE acc_dtls.record_status not like'D' and acc_dtls.dr_or_cr=@dr_or_cr "
                               + " and acc_dtls.tran_seq = @tran_seq "
                               + " ORDER BY acc_dtls.tran_dtls_seq DESC ";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.tran_dtls_seq = Convert.ToInt32(drc[i]["tran_dtls_seq"]);
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        info.account_name = drc[i]["acc_name"].ToString();
                        info.amount = Convert.ToDouble(drc[i]["amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERAL_Ledger> GetListFinanceStatus()
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();
                string strSQL = " SELECT record_status,record_status_name "
                               + " FROM fim_mas_record_statuses "
                               + " WHERE record_status not like 'D'"
                               + " ORDER BY  record_status_name ";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.record_status_code = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public M_ACM_GENERAL_Ledger GetListFinanceStatus_BySeq(Int32 record_status_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@record_status_code", record_status_code);

                strSQL = " SELECT record_status,record_status_name "
                        + " FROM fim_mas_record_statuses "
                        + " WHERE record_status not like 'D' and record_status = @record_status_code "
                        + " ORDER BY  record_status_name ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.record_status_code = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public Int32 GetListDataNotUpdateTranSeq()
        {
            Int32 itran_seq = 0;
            DataRowCollection drc = null;
            try
            {
                dbManager.Open();
                string strSQL = " SELECT case  when MAX(tran_seq)  is not null then MAX(tran_seq)  else 1 end as max_tran_seq "
                               + " FROM acm_transection ";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    itran_seq = Convert.ToInt32(drc[0]["max_tran_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return itran_seq;
        }

        //public string GetLatestTranSeq()
        //{
        //    string strTranSeq;
        //    string strSQL;

        //    try
        //    {
        //        strSQL = "select last_insert_id() as last_seq from acm_transection";
        //        strTranSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strTranSeq;
        //}

        public bool CheckDrCr(int TranSeq)
        {
            string strSQL = "";
            bool bOK = false;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                //dbManager.AddParameters(0, "@ref_type", info.ref_type);
                dbManager.AddParameters(0, "@tran_seq", TranSeq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select tran_seq"
                    + ", sum(coalesce(amount_dr, 0)) as amount_dr"
                    + ", sum(coalesce(amount_cr, 0)) as amount_cr"
                    + " from (select tran_seq"
                        + ", sum(case when dr_or_cr = 'Dr'"
                            + " then coalesce(amount, 0)"
                            + " else 0"
                            + " end) as amount_dr"
                        + ", sum(case when dr_or_cr = 'Dr'"
                            + " then 0"
                            + " else coalesce(amount, 0)"
                            + " end) as amount_cr"
                    //coalesce(amount, 0))
                    // as 
                        + " from acm_transection_dtls"
                        + " where tran_seq = @tran_seq"
                        + " and record_status not like @delete_status"
                        + " group by tran_seq, dr_or_cr"
                    + ") t"
                    + " group by tran_seq";

                var dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    bOK = Convert.ToDouble(dtb.Rows[0]["amount_dr"]) == Convert.ToDouble(dtb.Rows[0]["amount_cr"]);
                }

                return bOK;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public M_ACM_GENERAL_Ledger GetListFillBySeq(Int32 tran_seq, string tran_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@tran_seq", tran_seq);
                dbManager.AddParameters(1, "@tran_type", tran_type);

                strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                       + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                       + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                       + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                       + " ,acc_dtls.record_status ,acc_dtls.dr_or_cr "
                       + " ,acc_tran.created_by,acc_tran.created_when"
                       + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                       + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount,ref_type "
                       + " , acc_tran.bgm_source_seq ,acc_tran.work_area_seq,acc_tran.remarks,acc_tran.tran_grp_seq,acc_tran.fiscal_period "
                       + " FROM acm_transection AS acc_tran "
                       + " Left Join acm_transection_dtls AS acc_dtls  ON acc_tran.tran_seq = "
                       + " acc_dtls.tran_seq and acc_dtls.record_status not like 'D' "
                       + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                       + " Left Join acm_transection_group ON acm_transection_group.tran_grp_seq = acc_tran.tran_grp_seq "
                       + " WHERE acc_tran.record_status not like  'D' "
                       + " AND acc_dtls.tran_seq = @tran_seq "
                       + " AND acc_tran.tran_type = @tran_type "
                       + " ORDER BY acc_dtls.tran_dtls_seq DESC ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    //info.tran_dtls_seq = Convert.ToInt32(drc[0]["tran_dtls_seq"]);
                    //info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    //info.tran_date = cn.ConvertNullDateTime(drc[0]["tran_date"]);
                    //info.fiscal_period = cn.ConvertNullInt(drc[0]["fiscal_period"]);
                    //info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    //info.remark = drc[0]["remarks"].ToString();
                    //info.tran_name = drc[0]["tran_name"].ToString();
                    //info.account_seq = cn.ConvertNullInt(drc[0]["account_seq"]);
                    //info.account_name = drc[0]["acc_name"].ToString();
                    //info.amount = Convert.ToDouble(drc[0]["amount"]);
                    //info.dr_or_cr = drc[0]["dr_or_cr"].ToString();
                    info.tran_dtls_seq = cn.ConvertNullInt(drc[0]["tran_dtls_seq"]);
                    info.tran_seq = Convert.ToInt32(drc[0]["tran_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.account_seq = cn.ConvertNullInt(drc[0]["account_seq"]);
                    info.account_name = drc[0]["acc_name"].ToString();
                    info.tran_code = drc[0]["tran_code"].ToString();
                    info.tran_date = cn.ConvertNullDateTime(drc[0]["tran_date"]);
                    info.tran_name = drc[0]["tran_name"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.dr_or_cr = drc[0]["dr_or_cr"].ToString();
                    info.debit_amount = cn.ConvertNullDouble(drc[0]["dtls_debit_amount"]);
                    info.credit_amount = cn.ConvertNullDouble(drc[0]["dtls_credit_amount"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.remark = drc[0]["remarks"].ToString();
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);
                    info.fiscal_period = drc[0]["fiscal_period"].ToString();
                    info.created_by = Convert.ToInt32(drc[0]["created_by"]);
                    info.created_when = Convert.ToDateTime(drc[0]["created_when"]);

                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_ACM_GENERAL_Ledger GetListFillDebit_CrditBySeq(Nullable<int> tran_dtls_seq, string dr_or_cr)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@tran_dtls_seq", tran_dtls_seq);
                dbManager.AddParameters(1, "@dr_or_cr", dr_or_cr);

                strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_dtls.account_seq,acc_dtls.amount "
                        + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                        + " FROM acm_transection_dtls AS acc_dtls "
                        + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                        + " WHERE acc_dtls.record_status not like'D' and acc_dtls.dr_or_cr=@dr_or_cr "
                        + " and acc_dtls.tran_dtls_seq = @tran_dtls_seq "
                        + " ORDER BY acc_dtls.tran_dtls_seq DESC ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.tran_dtls_seq = Convert.ToInt32(drc[0]["tran_dtls_seq"]);
                    info.account_seq = cn.ConvertNullInt(drc[0]["account_seq"]);
                    info.account_name = drc[0]["acc_name"].ToString();
                    info.amount = Convert.ToDouble(drc[0]["amount"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public List<M_ACM_GENERAL_Ledger> GetDataToTransferAccount4and5(string BgmYear, string BgmSourceSeq)
        {
            string strSQL;
            DataTable dtb = null;
            List<M_ACM_GENERAL_Ledger> list = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_year", BgmYear);
                dbManager.AddParameters(1, "@bgm_source_seq", BgmSourceSeq);

                strSQL = @"
select acc.account_seq, acc.account_code, acc.account_name
, sum(case when td.dr_or_cr = 'Dr' then td.amount else -td.amount end) as amount
from acm_transection t
inner join acm_transection_dtls td on t.tran_seq = td.tran_seq and td.record_status <> 'D'
inner join acm_mas_account acc on td.account_seq = acc.account_seq
where t.record_status <> 'D'
and t.bgm_year = @bgm_year
and t.bgm_source_seq = @bgm_source_seq
and (acc.account_code like '4%' or acc.account_code like '5%')
group by acc.account_seq, acc.account_code, acc.account_name
order by acc.account_code";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                foreach (DataRow dr in dtb.Rows)
                {
                    double dAmount = Convert.ToDouble(dr["amount"]);

                    M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger()
                    {
                        tran_seq = cn.ConvertNullInt(BgmSourceSeq) ?? 1,
                        account_seq = cn.ConvertNullInt(dr["account_seq"]),
                        account_code = dr["account_code"].ToString(),
                        account_name = dr["account_name"].ToString(),
                        debit_amount = dAmount < 0 ? -dAmount : 0,
                        credit_amount = dAmount > 0 ? dAmount : 0,
                        dr_or_cr = dAmount > 0 ? "Dr" : "Cr",
                        amount = Math.Abs(dAmount),
                        record_status = "W"
                    };

                    list.Add(info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return list;
        }

        public void Insert_DB_CR(M_ACM_GENERAL_Ledger info) //acm_transection_dtls
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@tran_seq", info.tran_seq);
                dbManager.AddParameters(1, "@account_seq", info.account_seq);
                dbManager.AddParameters(2, "@dr_or_cr", info.dr_or_cr);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@record_status", info.record_status);
                dbManager.AddParameters(5, "@created_by", info.created_by);
                dbManager.AddParameters(6, "@created_when", info.created_when);

                strSQL = " INSERT INTO acm_transection_dtls "
                        + "(tran_seq,account_seq,dr_or_cr,amount,record_status,created_by,created_when)"
                        + " VALUES (@tran_seq,@account_seq,@dr_or_cr,@amount,@record_status,@created_by,@created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string Insert_Transaction(M_ACM_GENERAL_Ledger info, bool bCheckDuplicate)
        {
            string strSQL;
            string strSeq;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);
                dbManager.BeginTransaction();
                dbManager.AddParameters(0, "@fiscal_period", info.fiscal_period);
                dbManager.AddParameters(1, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(2, "@tran_code", info.tran_code);
                dbManager.AddParameters(3, "@tran_date", info.tran_date);
                dbManager.AddParameters(4, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(5, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(6, "@tran_name", info.tran_name);
                dbManager.AddParameters(7, "@remarks", info.remark);
                dbManager.AddParameters(8, "@record_status", info.record_status);
                dbManager.AddParameters(9, "@created_by", info.created_by);
                dbManager.AddParameters(10, "@created_when", info.created_when);
                dbManager.AddParameters(11, "@tran_type", info.tran_type);
                dbManager.AddParameters(12, "@ref_no", info.ref_no);
                dbManager.AddParameters(13, "@bgm_year", info.bgm_year);

                if (bCheckDuplicate)
                {
                    strSQL = "select count(*) as row_count"
                    + " from acm_transection"
                    + " where ref_no = @ref_no"
                    + " and DATEDIFF(SECOND, created_when, GETDATE()) < 30";
                    var exists = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    if (exists.Rows.Count > 0 && (cn.ConvertNullInt(exists.Rows[0][0]) ?? 0) > 0)
                    {
                        return null;
                    }
                }

                strSQL = " INSERT INTO acm_transection "
                        + " (fiscal_period,tran_grp_seq,tran_code,tran_date,work_area_seq,bgm_source_seq "
                        + " ,tran_name,remarks,record_status,created_by,created_when,tran_type,ref_no, bgm_year)"
                        + "  VALUES "
                        + " (@fiscal_period,@tran_grp_seq,@tran_code,@tran_date,@work_area_seq,@bgm_source_seq "
                        + " ,@tran_name,@remarks,@record_status,@created_by,@created_when,@tran_type,@ref_no, @bgm_year)"
                        + " select scope_identity();";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = "select last_insert_id() as last_seq from acm_transection";
                //strSQL = "INSERT INTO icm_bid_offer_collateral"
                //    + " (icm_col_type_seq, icm_bid_off_col_ref_no, icm_bid_off_col_amount"
                //    + ", icm_bid_off_col_descr, icm_bid_off_seq, receipt_no"
                //    + ", created_by, created_when)"
                //    + " VALUES(@icm_col_type_seq"
                //    + ", @icm_bid_off_col_ref_no"
                //    + ", @icm_bid_off_col_amount"
                //    + ", @icm_bid_off_col_descr"
                //    + ", @icm_bid_off_seq"
                //    + ", @receipt_no"

                //    + ", @created_by"
                //    + ", @created_when"
                //    + " );"
                //    + " select scope_identity();";
                //var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                //return strSeq;

                //strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return strSeq;
        }

        public void Insert_Transaction_Group(M_ACM_GENERAL_Ledger info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@tran_grp_code", info.tran_grp_code);
                dbManager.AddParameters(1, "@ref_type", info.ref_type);
                dbManager.AddParameters(2, "@record_status", info.record_status);
                dbManager.AddParameters(3, "@created_by", info.created_by);
                dbManager.AddParameters(4, "@created_when", info.created_when);

                strSQL = " INSERT INTO acm_transection_group "
                        + " (tran_grp_code,ref_type,record_status,created_by,created_when)"
                        + " VALUES "
                        + " (@tran_grp_code,@ref_type,@record_status,@created_by,@created_when)";



                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_DB_CR(M_ACM_GENERAL_Ledger info) //acm_transection_dtls
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@tran_dtls_seq", info.tran_dtls_seq);
                dbManager.AddParameters(1, "@tran_seq", info.tran_seq);
                dbManager.AddParameters(2, "@account_seq", info.account_seq);
                dbManager.AddParameters(3, "@dr_or_cr", info.dr_or_cr);
                dbManager.AddParameters(4, "@amount", info.amount);
                dbManager.AddParameters(5, "@record_status", info.record_status);
                dbManager.AddParameters(6, "@updated_by", info.updated_by);
                dbManager.AddParameters(7, "@updated_when", info.updated_when);


                strSQL = "  UPDATE acm_transection_dtls "
                        + "  SET tran_seq =@tran_seq "
                        + " ,account_seq =@account_seq "
                        + " ,dr_or_cr =@dr_or_cr "
                        + " ,amount = @amount "
                        + " ,record_status = @record_status "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE tran_dtls_seq = @tran_dtls_seq ";


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete_DB_CR(int tran_dtls_seq, int updated_by, DateTime updated_when) //acm_transection_dtls
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@tran_dtls_seq", tran_dtls_seq);
                dbManager.AddParameters(1, "@record_status", "D");
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", updated_when);


                strSQL = "UPDATE acm_transection_dtls "
                        + "  SET record_status = @record_status "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE tran_dtls_seq = @tran_dtls_seq ";


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_Transaction(M_ACM_GENERAL_Ledger info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                if (info.approve_status == true)
                {
                    dbManager.CreateParameters(15);
                    dbManager.AddParameters(0, "@tran_seq", info.tran_seq);
                    dbManager.AddParameters(1, "@fiscal_period", info.fiscal_period);
                    dbManager.AddParameters(2, "@tran_grp_seq", info.tran_grp_seq);
                    dbManager.AddParameters(3, "@tran_code", info.tran_code);
                    dbManager.AddParameters(4, "@tran_date", info.tran_date);
                    dbManager.AddParameters(5, "@work_area_seq", info.work_area_seq);
                    dbManager.AddParameters(6, "@bgm_source_seq", info.bgm_source_seq);
                    dbManager.AddParameters(7, "@tran_name", info.tran_name);
                    dbManager.AddParameters(8, "@remarks", info.remark);
                    dbManager.AddParameters(9, "@record_status", info.record_status);
                    dbManager.AddParameters(10, "@updated_by", info.updated_by);
                    dbManager.AddParameters(11, "@updated_when", info.updated_when);
                    dbManager.AddParameters(12, "@approved_by", info.approved_by);
                    dbManager.AddParameters(13, "@approved_when", info.approved_when);
                    dbManager.AddParameters(14, "@tran_type", info.tran_type);

                    strSQL = "  UPDATE acm_transection "
                        + "  SET fiscal_period =@fiscal_period "
                        + " ,tran_grp_seq =@tran_grp_seq "
                        + " ,tran_code =@tran_code "
                        + " ,tran_date = @tran_date "
                        + " ,work_area_seq = @work_area_seq "
                        + " ,bgm_source_seq = @bgm_source_seq "
                        + " ,tran_name = @tran_name "
                        + " ,remarks =@remarks "
                        + " ,record_status =@record_status "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " ,approved_by = @approved_by "
                        + " ,approved_when = @approved_when "
                        + " WHERE tran_seq = @tran_seq "
                        + " and tran_type = @tran_type ";
                }
                else
                {
                    dbManager.CreateParameters(13);
                    dbManager.AddParameters(0, "@tran_seq", info.tran_seq);
                    dbManager.AddParameters(1, "@fiscal_period", info.fiscal_period);
                    dbManager.AddParameters(2, "@tran_grp_seq", info.tran_grp_seq);
                    dbManager.AddParameters(3, "@tran_code", info.tran_code);
                    dbManager.AddParameters(4, "@tran_date", info.tran_date);
                    dbManager.AddParameters(5, "@work_area_seq", info.work_area_seq);
                    dbManager.AddParameters(6, "@bgm_source_seq", info.bgm_source_seq);
                    dbManager.AddParameters(7, "@tran_name", info.tran_name);
                    dbManager.AddParameters(8, "@remarks", info.remark);
                    dbManager.AddParameters(9, "@record_status", info.record_status);
                    dbManager.AddParameters(10, "@updated_by", info.updated_by);
                    dbManager.AddParameters(11, "@updated_when", info.updated_when);
                    dbManager.AddParameters(12, "@tran_type", info.tran_type);

                    strSQL = "  UPDATE acm_transection "
                        + "  SET fiscal_period =@fiscal_period "
                        + " ,tran_grp_seq =@tran_grp_seq "
                        + " ,tran_code =@tran_code "
                        + " ,tran_date = @tran_date "
                        + " ,work_area_seq = @work_area_seq "
                        + " ,bgm_source_seq = @bgm_source_seq "
                        + " ,tran_name = @tran_name "
                        + " ,remarks =@remarks "
                        + " ,record_status =@record_status "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE tran_seq = @tran_seq "
                        + " and tran_type = @tran_type ";
                }




                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_Transaction_Group(M_ACM_GENERAL_Ledger info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(1, "@tran_grp_code", info.tran_grp_code);
                dbManager.AddParameters(2, "@ref_type", info.ref_type);
                dbManager.AddParameters(3, "@record_status", info.record_status);
                dbManager.AddParameters(4, "@updated_by", info.updated_by);
                dbManager.AddParameters(5, "@updated_when", info.updated_when);

                strSQL = "  UPDATE acm_transection_group "
                        + "  SET tran_grp_code =@tran_grp_code "
                        + " ,ref_type =@ref_type "
                        + " ,record_status =@record_status "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE tran_grp_seq = @tran_grp_seq ";


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetIncomeVsOutcome(string strBgmSourceSeq, DateTime dtStart, DateTime dtEnd, int nBgmYear)
        {
            DataTable dtb = null;
            string strSQL = "";
            double dResult = 0;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@dtStart", dtStart);
                dbManager.AddParameters(1, "@dtEnd", dtEnd);
                dbManager.AddParameters(2, "@BgmSourceSeq", strBgmSourceSeq);
                dbManager.AddParameters(3, "@nBgmYear", nBgmYear);

                strSQL = "select sum(case left(a.account_code, 1)"
                        + " when '1' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                + " end"

                        + " when '2' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '3' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                        + " end"

                        + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                        + " when 'Cr' then coalesce(amount,0)"
                                        + " end"

                        + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                        + " when 'Cr' then coalesce(amount,0)"
                                        + " end"
                        + " end"
                        + ") as amount"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"
                    + " where left(a.account_code, 1) in ('4', '5')"
                    + " and t.record_status not like 'D'"
                    + " and td.record_status not like 'D'"
                    //+ " and t.tran_date >= @dtStart"
                    //+ " and t.tran_date <= @dtEnd"
                    + " and t.bgm_year = @nBgmYear"
                    + " and t.bgm_source_seq = @BgmSourceSeq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    dResult = cn.ConvertNullDouble(dtb.Rows[0][0]) ?? 0;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dResult;
        }

        public DataTable GetTrasfer(string strBgmSourceSeq, DateTime dtStart, DateTime dtEnd)
        {
            DataTable dtb = null;
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@dtStart", dtStart);
                dbManager.AddParameters(1, "@dtEnd", dtEnd);
                dbManager.AddParameters(2, "@BgmSourceSeq", strBgmSourceSeq);

                strSQL = "select sum(case left(a.account_code, 1)"
                        + " when '1' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                + " end"

                        + " when '2' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '3' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                        + " end"

                        + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                        + " when 'Cr' then coalesce(amount,0)"
                                        + " end"

                        + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                        + " when 'Cr' then coalesce(amount,0)"
                                        + " end"
                        + " end"
                        + ") as amount"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"
                    + " where left(a.account_code, 1) in ('4', '5')"
                    + " and t.record_status = 'A'"
                    + " and td.record_status not like 'D'"
                    + " and t.tran_date >= @dtStart"
                    + " and t.tran_date <= @dtEnd"
                    + " and t.bgm_source_seq = @BgmSourceSeq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Report_BOOK_General(string pReportType, string StartDate, string EndDate, string SourceSeq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);
                dbManager.AddParameters(2, "@SourceSeq", SourceSeq);

                if (pReportType == "All")
                {
                    strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq, coalesce(acc_tran.ref_no, acc_tran.tran_code) as ref_no"
                        + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                        + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                        + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                        + " ,acc_dtls.record_status"
                        + " ,acc_tran.created_by,acc_tran.created_when "
                        + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                        + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount "
                        //+ " , fiscal.fiscal_period_name "  ตัดออก
                        + " , case when acc_tran.tran_type = 'G' then 'บัญชีทั่วไป (General)' "
                        + " when acc_tran.tran_type = 'P' then 'บัญชีจ่าย (Payment)' "
                        + " when acc_tran.tran_type = 'R' then 'บัญชีรับ (Receive)' "
                        + " when acc_tran.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)' "
                        + " end tran_type,acc_tran.approved_by "
                        + " FROM acm_transection AS acc_tran "
                        + " Left Join acm_transection_dtls AS acc_dtls ON acc_tran.tran_seq = acc_dtls.tran_seq and acc_dtls.record_status <> 'D'"
                        + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                        // ตัด left join acm_mas_fiscal_period ออก
                        //+ " Left Join acm_mas_fiscal_period AS fiscal ON acc_tran.fiscal_period = fiscal.fiscal_period "
                        + " WHERE acc_tran.record_status not like  'D' "
                        + " AND acc_tran.bgm_source_seq in (" + SourceSeq + ") "
                        + " AND acc_tran.tran_date >= @StartDate and acc_tran.tran_date <= @EndDate ";
                    //+ " AND fiscal.record_status not like  'D' AND acc_tran.approved_by is not null "
                    // เปลี่ยน order by fiscal.fiscal_period เป็น acc_tran.fiscal_period
                    //+ " ORDER BY fiscal.fiscal_period,acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.dr_or_cr DESC ";
                    //+ " ORDER BY acc_tran.tran_date,acc_tran.ref_no, acc_tran.tran_seq,acc_dtls.dr_or_cr DESC ";
                }
                else
                {
                    strSQL = " SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq, coalesce(acc_tran.ref_no, acc_tran.tran_code) as ref_no"
                        + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                        + " ,concat(coalesce(account_code,''),' : ',coalesce(account_name,'')) AS acc_name "
                        + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                        + " ,acc_dtls.record_status"
                        + " ,acc_tran.created_by,acc_tran.created_when "
                        + " , case when  acc_dtls.dr_or_cr = 'Dr' then acc_dtls.amount else 0 end as dtls_debit_amount "
                        + " , case when acc_dtls.dr_or_cr = 'Cr' then acc_dtls.amount  else 0 end as dtls_credit_amount "
                        //+ " , fiscal.fiscal_period_name " ตัดออก
                        + " , case when acc_tran.tran_type = 'G' then 'บัญชีทั่วไป (General)' "
                        + " when acc_tran.tran_type = 'P' then 'บัญชีจ่าย (Payment)' "
                        + " when acc_tran.tran_type = 'R' then 'บัญชีรับ (Receive)' "
                        + " when acc_tran.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)' "
                        + " end tran_type,acc_tran.approved_by "
                        + " FROM acm_transection AS acc_tran "
                        + " Left Join acm_transection_dtls AS acc_dtls ON acc_tran.tran_seq = acc_dtls.tran_seq and acc_dtls.record_status <> 'D'"
                        + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                        // ตัด left join acm_mas_fiscal_period ออก
                        //+ " Left Join acm_mas_fiscal_period AS fiscal ON acc_tran.fiscal_period = fiscal.fiscal_period "
                        + " WHERE acc_tran.record_status not like  'D' "
                        + " AND acc_tran.bgm_source_seq in (" + SourceSeq + ") "
                        + (StartDate != null && EndDate != null ? "and acc_tran.tran_date >= @StartDate and acc_tran.tran_date <= @EndDate " : "");
                    //+ " ORDER BY acc_tran.tran_date,acc_tran.ref_no, acc_tran.tran_seq,acc_dtls.dr_or_cr DESC ";

                    if (pReportType == "General")
                    {
                        strSQL += " AND acc_tran.tran_type = 'G' ";
                    }
                    else if (pReportType == "Receive")
                    {
                        strSQL += " AND acc_tran.tran_type = 'R' ";
                    }
                    else if (pReportType == "Payment")
                    {
                        strSQL += " AND acc_tran.tran_type = 'P' ";
                    }
                    else if (pReportType == "Adjust")
                    {
                        strSQL += " AND acc_tran.tran_type = 'A' ";
                    }
                    // เปลี่ยน order by fiscal.fiscal_period เป็น acc_tran.fiscal_period
                    //strSQL = strSQL + " ORDER BY fiscal.fiscal_period,acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.dr_or_cr DESC ";
                    strSQL += " ORDER BY acc_tran.tran_date,acc_tran.ref_no, acc_tran.tran_seq,acc_dtls.dr_or_cr DESC ";
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable ReportPaymentDetail()
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                //dbManager.CreateParameters(2);
                //dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(1, "@tran_grp_seq", tran_grp_seq);

                strSQL = "select p.tran_grp_seq"
                    + ", case when p.method_seq = 2"
                        + " then che.pay_date"
                        + " else p.real_pay_date"
                        + " end as real_pay_date"
                    + ", che.cheque_no, con.NameTh, p.amount"
                    + ", b.bank_name, ba.bank_acc_no"

                    + " from fim_payments p"
                    + " left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq"
                    + " left join fim_cheques che on pc.cheque_seq = che.cheque_seq"
                    + " left join fim_mas_bank_accounts ba on che.bank_acc_seq = ba.bank_acc_seq"
                    + " left join fim_mas_bank_branchs bb on ba.branch_seq = bb.branch_seq"
                    + " left join fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    + " left join IcmMasContact con on p.contact_seq = con.ConId"
                    + " where p.record_status = 'A'"
                    + " and p.tran_grp_seq is not null"
                    ;
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_DIVIDE_Type(DateTime StartDate, DateTime EndDate, string SourceSeq, string AccountSeq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);
                dbManager.AddParameters(2, "@SourceSeq", SourceSeq);
                dbManager.AddParameters(3, "@AccountSeq", AccountSeq);

                strSQL = @"select t.account_seq, t.account_code, t.account_name
, t.tran_seq, t.tran_code, t.tran_name
, t.tran_type, t.remarks, t.dr_or_cr
, t.tran_type_name, t.tran_date, t.ref_no as sub_account_code
, sum(coalesce(t.amount_dr, 0)) as amount_dr
, sum(coalesce(t.amount_cr, 0)) as amount_cr
, case when coalesce(prev_amount_dr, 0) - coalesce(prev_amount_cr, 0) > 0 then coalesce(prev_amount_dr, 0) - coalesce(prev_amount_cr, 0) else 0 end as prev_amount_dr
, case when coalesce(prev_amount_dr, 0) - coalesce(prev_amount_cr, 0) > 0 then 0 else coalesce(prev_amount_cr, 0) - coalesce(prev_amount_dr, 0) end as prev_amount_cr
, sum(coalesce(t.amount_dr, 0)) + coalesce(t.prev_amount_dr, 0) as sum_dr
, sum(coalesce(t.amount_cr, 0)) + coalesce(t.prev_amount_cr, 0) as sum_cr
from (
SELECT acm.account_seq, acm.account_code, acm.account_name
, trans.tran_seq, trans.tran_code, trans.tran_name
, trans.tran_type, trans.remarks, trans.dr_or_cr
, trans.tran_date
, case when trans.tran_type = 'A' then trans.tran_code else trans.ref_no end as ref_no
, case when trans.dr_or_cr = 'Dr' then trans.amount else 0 end as amount_dr
, case when trans.dr_or_cr = 'Cr' then trans.amount else 0 end as amount_cr
, case 
	when trans.tran_type = 'G' then 'บัญชีทั่วไป (General)'  
	when trans.tran_type = 'P' then 'บัญชีจ่าย (Payment)'  
	when trans.tran_type = 'R' then 'บัญชีรับ (Receive)'  
	when trans.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)'  
	end as tran_type_name 
, prev.amount_dr as prev_amount_dr
, prev.amount_cr as prev_amount_cr

FROM acm_mas_account  acm 
inner Join acm_mas_account_version  acmv ON acm.version_seq = acmv.version_seq 
	AND acmv.record_status = 'U' 
	AND acmv.use_flag = 1 
LEFT JOIN (select t.account_seq
    , sum(coalesce(t.amount_dr, 0)) amount_dr
    , sum(coalesce(t.amount_cr, 0)) amount_cr

    from (select td.account_seq
        , case when td.dr_or_cr = 'Dr' then td.amount else 0 end as amount_dr
        , case when td.dr_or_cr = 'Cr' then td.amount else 0 end as amount_cr
	    from acm_transection_dtls td
	    left join acm_transection t on td.tran_seq = t.tran_seq
	    where td.record_status <> 'D' 
	    and t.record_status <> 'D' 
	    and t.bgm_source_seq in (" + SourceSeq + @") 
	    and t.tran_date < @StartDate
    ) t
    group by t.account_seq
) prev ON acm.account_seq = prev.account_seq 

LEFT JOIN (select trans.tran_seq, trans.tran_code, trans.tran_name
    , trans.tran_type, trans.remarks, tran_dtls.dr_or_cr, trans.ref_no
    , trans.tran_date, tran_dtls.amount, tran_dtls.account_seq
	from acm_transection_dtls tran_dtls
	LEFT JOIN acm_transection trans ON tran_dtls.tran_seq = trans.tran_seq 
	where trans.bgm_source_seq in (" + SourceSeq + @") 
	and trans.tran_date >= @StartDate 
	and trans.tran_date <= @EndDate
	and trans.record_status <> 'D'
	and tran_dtls.record_status <> 'D'
) trans on acm.account_seq = trans.account_seq

WHERE acm.record_status = 'U'"
+ (!string.IsNullOrEmpty(AccountSeq) ? " and acm.account_seq = @AccountSeq" : "")
+ @" and (prev.amount_dr > 0 or prev.amount_cr > 0 or trans.amount > 0)
) t 
group by t.account_seq, t.account_code, t.account_name
    , t.tran_seq, t.tran_code, t.tran_name
    , t.tran_type, t.remarks, t.dr_or_cr
    , t.tran_type_name, t.tran_date, t.ref_no
    , t.amount_dr, t.amount_cr
    , t.prev_amount_dr, t.prev_amount_cr
ORDER BY t.account_code,t.tran_date,t.ref_no";

                //                strSQL = @"
                //select t.account_seq, t.account_code, t.account_name
                //, t.tran_seq, t.tran_code, t.tran_name
                //, t.tran_type, t.remarks, t.dr_or_cr
                //, t.tran_type_name, t.tran_date
                //, t.amount_dr, t.amount_cr
                //, t.prev_amount_dr, t.prev_amount_cr
                //, sum(coalesce(t.amount_dr, 0)) + coalesce(t.prev_amount_dr, 0) as sum_dr
                //, sum(coalesce(t.amount_cr, 0)) + coalesce(t.prev_amount_cr, 0) as sum_cr
                //from (
                //SELECT acm.account_seq, acm.account_code, acm.account_name
                //, trans.tran_seq, trans.tran_code, trans.tran_name
                //, trans.tran_type, trans.remarks, tran_dtls.dr_or_cr
                //, trans.tran_date
                //, case when tran_dtls.dr_or_cr = 'Dr' then tran_dtls.amount else 0 end as amount_dr
                //, case when tran_dtls.dr_or_cr = 'Cr' then tran_dtls.amount else 0 end as amount_cr
                //, case 
                //	when trans.tran_type = 'G' then 'บัญชีทั่วไป (General)'  
                //	when trans.tran_type = 'P' then 'บัญชีจ่าย (Payment)'  
                //	when trans.tran_type = 'R' then 'บัญชีรับ (Receive)'  
                //	when trans.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)'  
                //	end as tran_type_name 
                //, prev.amount_dr as prev_amount_dr
                //, prev.amount_cr as prev_amount_cr
                //FROM acm_mas_account  acm 
                //inner Join acm_mas_account_version  acmv ON acm.version_seq = acmv.version_seq 
                //	AND acmv.record_status = 'U' 
                //	AND acmv.use_flag = 1 
                //LEFT JOIN (select t.account_seq
                //    , sum(coalesce(t.amount_dr, 0)) amount_dr
                //    , sum(coalesce(t.amount_cr, 0)) amount_cr
                //
                //    from (select td.account_seq
                //        , case when td.dr_or_cr = 'Dr' then td.amount else 0 end as amount_dr
                //        , case when td.dr_or_cr = 'Cr' then td.amount else 0 end as amount_cr
                //	    from acm_transection_dtls td
                //	    left join acm_transection t on td.tran_seq = td.tran_seq
                //	    where td.record_status <> 'D' 
                //	    and t.record_status <> 'D' 
                //	    and t.bgm_source_seq in (" + SourceSeq + @") 
                //	    and t.tran_date < @StartDate
                //    ) t
                //    group by t.account_seq
                //) prev ON acm.account_seq = prev.account_seq 
                //
                //LEFT JOIN acm_transection_dtls tran_dtls ON acm.account_seq = tran_dtls.account_seq
                //LEFT JOIN acm_transection trans ON tran_dtls.tran_seq = trans.tran_seq 
                //	and trans.bgm_source_seq in (" + SourceSeq + @") 
                //	and trans.tran_date >= @StartDate 
                //	and trans.tran_date <= @EndDate
                //WHERE acm.record_status = 'U'"
                //+ (!string.IsNullOrEmpty(AccountSeq) ? " and acm.account_seq = @AccountSeq" : "")
                //+ @"and (prev.amount_dr > 0 or prev.amount_cr > 0 or tran_dtls.amount > 0)
                //) t
                //group by t.account_seq, t.account_code, t.account_name
                //, t.tran_seq, t.tran_code, t.tran_name
                //, t.tran_type, t.remarks, t.dr_or_cr
                //, t.tran_type_name, t.tran_date
                //, t.amount_dr, t.amount_cr
                //, t.prev_amount_dr, t.prev_amount_cr
                //ORDER BY t.account_code,t.tran_date,t.tran_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public IList<M_ACM_ReportLedger> Report_Ledger(DateTime StartDate, DateTime EndDate, string SourceSeq)
        {
            string strSQL;
            DataTable dtb = null;
            var models = new List<M_ACM_ReportLedger>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);
                dbManager.AddParameters(2, "@SourceSeq", SourceSeq);

                strSQL = "SELECT  acm1.account_seq,acm1.account_code, acm1.account_name,trans.tran_seq,trans.tran_code,trans.tran_name"
                    + ", tran_dtls.dr_or_cr,tran_dtls.amount,trans.tran_type, tran_dtls2.account_seq as sub_account_seq, tran_dtls2.tran_seq"
                    + ", acm.account_code as sub_account_code,acm.account_name as sub_account_name,tran_dtls2.dr_or_cr as sub_dr_or_cr"
                    + ", tran_dtls2.amount as sub_amount"
                    + " , case when trans.tran_type = 'G' then 'บัญชีทั่วไป (General)' "
                        + " when trans.tran_type = 'P' then 'บัญชีจ่าย (Payment)' "
                        + " when trans.tran_type = 'R' then 'บัญชีรับ (Receive)' "
                        + " when trans.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)' "
                        + " end tran_type_name ,trans.remarks,trans.tran_date "
                    + " FROM acm_mas_account  acm1 "
                    + " Left Join acm_mas_account_version  acmv ON acm1.version_seq = acmv.version_seq AND acmv.record_status = 'U'"
                    + " AND acmv.use_flag = 1 and acmv.version_seq is not null "
                    + " LEFT JOIN (select tran_seq, account_seq, dr_or_cr"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " from acm_transection_dtls"
                        + " where record_status <> 'D'"
                        + " group by tran_seq, account_seq, dr_or_cr"
                        + ") tran_dtls ON acm1.account_seq = tran_dtls.account_seq"
                    + " LEFT JOIN acm_transection_dtls tran_dtls2 ON tran_dtls.tran_seq = tran_dtls2.tran_seq AND tran_dtls2.account_seq not like tran_dtls.account_seq"
                    + " LEFT JOIN acm_mas_account acm ON tran_dtls2.account_seq = acm.account_seq"
                    + " LEFT JOIN acm_transection trans ON tran_dtls.tran_seq = trans.tran_seq"// and trans.record_status = 'A'"
                    + " WHERE acmv.record_status = 'U'"
                    + " and trans.bgm_source_seq in (" + SourceSeq + ")"
                    + " and trans.tran_date >= @StartDate and trans.tran_date <= @EndDate"
                    + " ORDER BY acm1.account_code, trans.tran_date,trans.tran_seq,tran_dtls.dr_or_cr DESC";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


                foreach (DataRow dr in dtb.Rows)
                {
                    var model = new M_ACM_ReportLedger
                    {
                        HeadAccountSeq = cn.ConvertNullInt(dr["account_seq"]) ?? 0,
                        HeadAccountCode = dr["account_code"].ToString(),
                        HeadAccountName = dr["account_name"].ToString(),
                        TranSeq = cn.ConvertNullInt(dr["tran_seq"]) ?? 0,
                        TranDate = (cn.ConvertNullDateTime(dr["tran_date"]) ?? DateTime.Today).ToShortDateString(),
                        TranCode = dr["tran_code"].ToString(),
                        TranName = dr["tran_name"].ToString(),
                        TranType = dr["tran_type"].ToString(),
                        TranTypeName = dr["tran_type_name"].ToString(),
                        TranRemarks = dr["remarks"].ToString(),
                        PairAccountSeq = cn.ConvertNullInt(dr["sub_account_seq"]) ?? 0,
                        PairAccountCode = dr["sub_account_code"].ToString(),
                        PairAccountName = dr["sub_account_name"].ToString(),
                        PairDrOrCr = dr["sub_dr_or_cr"].ToString(),
                        PairAmount = (cn.ConvertNullDouble(dr["sub_amount"]) ?? 0).ToString("#,##0.00"),
                    };

                    models.Add(model);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return models;
        }

        public DataTable Report_ACCOUNT_Experiment(string StartDate, string EndDate)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);

                strSQL = "  SELECT acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                         + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                         + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                         + " ,acc_tran.record_status ,acc_tran.created_by,acc_tran.created_when "
                         + " FROM acm_transection AS acc_tran "
                         + " Left Join acm_transection_dtls AS acc_dtls ON acc_tran.tran_seq = acc_dtls.tran_seq "
                         + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                         + " Left Join acm_mas_account_version as acc_ver ON acc.version_seq = acc_ver.version_seq "
                         + " and acc_ver.record_status = 'U' "
                         + " WHERE acc_tran.record_status = 'A' "
                         + " and acc_tran.tran_date >= @StartDate and acc_tran.tran_date<= @EndDate "
                    // + " AND  acc_tran.approved_by is not null "
                         + " ORDER BY acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.dr_or_cr DESC  ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_ACCOUNT_EXPERIMENT_FISCAL(string pFiscalStartName, string pFiscalEndName)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();

                strSQL = "  SELECT   acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.account_seq "
                         + " ,acc_dtls.dr_or_cr,acc_dtls.amount,acc.account_code,acc.account_name "
                         + " ,acc_tran.tran_date,acc_tran.tran_code,acc_tran.tran_name "
                         + " ,acc_dtls.record_status ,acc_tran.created_by,acc_tran.created_when "
                    //+ " ,fiscal.fiscal_period_code,fiscal.fiscal_period_name "
                         + " ,acc_tran.fiscal_period "
                         + "  FROM acm_transection AS acc_tran "
                         + "  Left Join acm_transection_dtls AS acc_dtls ON acc_tran.tran_seq = acc_dtls.tran_seq "
                         + " Left Join acm_mas_account AS acc ON acc_dtls.account_seq = acc.account_seq "
                         + " Left Join acm_mas_account_version as acc_ver ON acc.version_seq = acc_ver.version_seq "
                         + "  and acc_ver.record_status = 'U' "
                    // ตัด join ออก
                    //+" Left Join acm_mas_fiscal_period as fiscal ON acc_tran.fiscal_period = fiscal.fiscal_period and "
                    //+" fiscal.record_status not like 'D' "
                         + " WHERE acc_tran.record_status not like  'D' "
                    //+ " AND  acc_tran.approved_by is not null and (fiscal.fiscal_period_name >='"+pFiscalStartName+"' and fiscal.fiscal_period_name <='"+pFiscalEndName+"')"
                         + " ORDER BY acc_dtls.tran_dtls_seq,acc_tran.tran_seq,acc_dtls.dr_or_cr DESC  ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable REPORT_BALANCE_Sheet()
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();

                strSQL = "SELECT tran_dtls.account_seq,acm1.account_code, acm1.account_name,trans.tran_seq,trans.tran_code,trans.tran_name "
                        + " ,tran_dtls.dr_or_cr,tran_dtls.amount,trans.tran_type ,tran_dtls2.tran_seq,tran_dtls2.account_seq,acm.account_code as sub_account_code,acm.account_name as sub_account_name,tran_dtls2.dr_or_cr as sub_dr_or_cr "
                        + " ,tran_dtls2.amount as sub_amount,case when trans.tran_type = 'G' then 'บัญชีทั่วไป (General)' "
                        + " when trans.tran_type = 'P' then 'บัญชีจ่าย (Payment)' "
                        + " when trans.tran_type = 'R' then 'บัญชีรับ (Receive)' "
                        + " when trans.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)' "
                        + " end tran_type_name ,trans.remarks "
                        + " , case when trans.tran_date is  null then  GETDATE() "
                        + " when trans.tran_date is not null then trans.tran_date end tran_date "
                    // ตัด ฟิลด์ fiscal.fiscal_period_name ออก
                    //+ " ,subacc.account_code as group_account_code,subacc.account_name as group_account_name,fiscal.fiscal_period_name "
                        + " ,subacc.account_code as group_account_code,subacc.account_name as group_account_name "
                        + " ,case when left(acm1.account_code,1) = '4' then  coalesce(tran_dtls2.amount,0) else 0 end profit "
                        + " ,case when left(acm1.account_code,1) = '5' then coalesce(tran_dtls2.amount,0) else 0 end loss "
                        + " FROM acm_mas_account  acm1 "
                        + " LEFT JOIN acm_mas_account  acm2 "
                        + " ON acm1.account_seq = acm2.upper_account_seq  "
                        + " and acm2.record_status = 'U' AND  acm1.record_status = 'U' and acm2.account_seq is null "
                        + " Left Join acm_mas_account_version  acmv ON acm1.version_seq = acmv.version_seq AND acmv.record_status = 'U' "
                        + " AND acmv.use_flag = 1 and acmv.version_seq is not null "
                        + " LEFT JOIN acm_transection_dtls tran_dtls ON acm1.account_seq = tran_dtls.account_seq "
                        + " LEFT JOIN acm_transection_dtls tran_dtls2 ON tran_dtls.tran_seq = tran_dtls2.tran_seq AND tran_dtls2.account_seq not like tran_dtls.account_seq "
                        + " LEFT JOIN acm_mas_account acm ON tran_dtls2.account_seq = acm.account_seq "
                        + " LEFT JOIN acm_transection trans ON tran_dtls2.tran_seq = trans.tran_seq "
                    //+ " Left Join acm_mas_fiscal_period AS fiscal ON trans.fiscal_period = fiscal.fiscal_period "
                        + " Left Join (SELECT  acm1.account_seq,left(acm1.account_code,2) as subgroup_account_code,concat(left(acm1.account_code,2),'00000000') as sub_account_code ,acm1.account_code,acm1.account_name "
                        + " FROM acm_mas_account as acm1 "
                        + " WHERE concat(left(acm1.account_code,2),'00000000')=acm1.account_code) as subacc ON  left(acm1.account_code,2)= subacc.subgroup_account_code "
                        + " WHERE  acmv.record_status = 'U'  and (left(acm1.account_code,1)not like '4' and left(acm1.account_code,1) not like '5')"
                        + " ORDER BY subacc.account_code,acm1.account_code  ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable REPORT_PROFIT_AND_LOSS()
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();

                strSQL = "  SELECT  tran_dtls.account_seq,acm1.account_code, acm1.account_name,tran.tran_seq,tran.tran_code,tran.tran_name "
                        + " ,tran_dtls.dr_or_cr,tran_dtls.amount,tran.tran_type ,tran_dtls2.tran_seq,tran_dtls2.account_seq,acm.account_code as sub_account_code,acm.account_name as sub_account_name,tran_dtls2.dr_or_cr as sub_dr_or_cr "
                        + " ,tran_dtls2.amount as sub_amount,case when tran.tran_type = 'G' then 'บัญชีทั่วไป (General)' "
                        + " when tran.tran_type = 'P' then 'บัญชีจ่าย (Payment)' "
                        + " when tran.tran_type = 'R' then 'บัญชีรับ (Receive)' "
                        + " when tran.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)' "
                        + " end tran_type_name ,tran.remarks "
                        + " , case when tran.tran_date is  null then  now() "
                        + " when tran.tran_date is not null then tran.tran_date end tran_date "
                        + " ,subacc.account_code as group_account_code,subacc.account_name as group_account_name,fiscal.fiscal_period_name "
                        + " ,case when left(acm1.account_code,1) = '4' then  coalesce(tran_dtls2.amount,0) else 0 end profit "
                        + " ,case when left(acm1.account_code,1) = '5' then coalesce(tran_dtls2.amount,0) else 0 end loss "
                        + " FROM acm_mas_account  acm1 "
                        + " LEFT JOIN acm_mas_account  acm2 "
                        + " ON acm1.account_seq = acm2.upper_account_seq  "
                        + " and acm2.record_status = 'U' AND  acm1.record_status = 'U' and acm2.account_seq is null "
                        + " Left Join acm_mas_account_version  acmv ON acm1.version_seq = acmv.version_seq AND acmv.record_status = 1 "
                        + " AND acmv.use_flag = 1 and acmv.version_seq is not null "
                        + " LEFT JOIN acm_transection_dtls tran_dtls ON acm1.account_seq = tran_dtls.account_seq "
                        + " LEFT JOIN acm_transection_dtls tran_dtls2 ON tran_dtls.tran_seq = tran_dtls2.tran_seq AND tran_dtls2.account_seq not like tran_dtls.account_seq "
                        + " LEFT JOIN acm_mas_account acm ON tran_dtls2.account_seq = acm.account_seq "
                        + " LEFT JOIN acm_transection tran ON tran_dtls2.tran_seq = tran.tran_seq "
                        + " Left Join acm_mas_fiscal_period AS fiscal ON tran.fiscal_period = fiscal.fiscal_period "
                        + " Left Join (SELECT  acm1.account_seq,left(acm1.account_code,2) as subgroup_account_code,concat(left(acm1.account_code,2),'00000000') as sub_account_code ,acm1.account_code,acm1.account_name "
                        + " FROM acm_mas_account as acm1 "
                        + " WHERE concat(left(acm1.account_code,2),'00000000')=acm1.account_code) as subacc ON  left(acm1.account_code,2)= subacc.subgroup_account_code "
                        + " WHERE  acmv.record_status = 'U'  and (left(acm1.account_code,1)='4' Or left(acm1.account_code,1) ='5')"
                        + " ORDER BY subacc.account_code,acm1.account_code  ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable GetBalanceSheet1(DateTime StartDate, DateTime EndDate)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);

                strSQL = " select t.account_code, t.account_name, t.gfmis_code "
                         + " , sum(coalesce(t.amount1, 0)) as amount1 "
                         + " , sum(coalesce(t.amount2, 0)) as amount2 "
                         + " , sum(coalesce(t.amount1, 0)) + sum(coalesce(t.amount2, 0)) as amount3 "
                         + " from (select a.account_code, a.account_name, a.gfmis_code "
                         + " , case when t.bgm_source_seq = 1 "
                         + " then case when td.dr_or_cr = 'Dr' "
                         + " then td.amount "
                         + " else -td.amount "
                         + " end "
                         + " else "
                         + " 0 "
                         + " end as amount1 "
                         + " , case when t.bgm_source_seq = 11 "
                         + " then case when td.dr_or_cr = 'Dr' "
                         + " then td.amount "
                         + " else -td.amount "
                         + " end "
                         + " else "
                         + " 0 "
                         + " end as amount2 "
                         + " from acm_transection_dtls td "
                         + " inner join acm_transection t on td.tran_seq = t.tran_seq "
                         + " inner join acm_mas_account a on td.account_seq = a.account_seq "
                         + " where a.account_code like '1%'"
                         + " and t.record_status = 'A'"
                         + " and t.tran_date >= @StartDate and t.tran_date <= @EndDate "
                         + " ) t"
                         + " group by t.account_code, t.account_name, t.gfmis_code ";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetBalanceSheet2(DateTime StartDate, DateTime EndDate)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);

                strSQL = " select t.account_code, t.account_name, t.gfmis_code "
                         + " , sum(coalesce(t.amount1, 0)) as amount1 "
                         + " , sum(coalesce(t.amount2, 0)) as amount2 "
                         + " , sum(coalesce(t.amount1, 0)) + sum(coalesce(t.amount2, 0)) as amount3 "
                         + " from (select a.account_code, a.account_name, a.gfmis_code "
                         + " , case when t.bgm_source_seq = 1 "
                         + "then case when td.dr_or_cr = 'Dr' "
                         + "then td.amount "
                         + "else -td.amount "
                         + " end "
                         + " else "
                         + " 0 "
                         + " end as amount1 "
                         + " , case when t.bgm_source_seq = 11 "
                         + " then case when td.dr_or_cr = 'Dr' "
                         + " then td.amount "
                         + " else -td.amount "
                         + " end "
                         + " else "
                         + " 0 "
                         + " end as amount2 "
                         + " from acm_transection_dtls td "
                         + " inner join acm_transection t on td.tran_seq = t.tran_seq "
                         + " inner join acm_mas_account a on td.account_seq = a.account_seq "
                         + " where a.account_code like '2%'"
                         + " and t.record_status = 'A'"
                         + " and t.tran_date >= @StartDate and t.tran_date <= @EndDate "
                         + " ) t"
                         + " group by t.account_code, t.account_name, t.gfmis_code ";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetBalanceSheet3(DateTime StartDate, DateTime EndDate)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@StartDate", StartDate);
                dbManager.AddParameters(1, "@EndDate", EndDate);

                strSQL = " select t.account_code, t.account_name, t.gfmis_code "
                         + " , sum(coalesce(t.amount1, 0)) as amount1 "
                         + " , sum(coalesce(t.amount2, 0)) as amount2 "
                         + " , sum(coalesce(t.amount1, 0)) + sum(coalesce(t.amount2, 0)) as amount3 "
                         + " from (select a.account_code, a.account_name, a.gfmis_code "
                         + " , case when t.bgm_source_seq = 1 "
                         + " then case when td.dr_or_cr = 'Dr' "
                         + " then td.amount "
                         + " else -td.amount "
                         + " end "
                         + " else "
                         + " 0 "
                         + " end as amount1 "
                         + " , case when t.bgm_source_seq = 11 "
                         + " then case when td.dr_or_cr = 'Dr' "
                         + " then td.amount "
                         + " else -td.amount "
                         + " end "
                         + " else "
                         + " 0 "
                         + " end as amount2 "
                         + " from acm_transection_dtls td "
                         + " inner join acm_transection t on td.tran_seq = t.tran_seq "
                         + " inner join acm_mas_account a on td.account_seq = a.account_seq "
                         + " where a.account_code like '3%'"
                         + " and t.record_status = 'A'"
                         + " and t.tran_date >= @StartDate and t.tran_date <= @EndDate "
                         + " ) t"
                         + " group by t.account_code, t.account_name, t.gfmis_code ";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportBalanceSheetHeader(string strAccountGroup)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select a.account_seq as account_seq_main, a2.account_seq as account_seq_sub"
                    + ", a.account_code as account_code_main, a2.account_code as account_code_sub"
                    + ", a.account_name as account_name_main, a2.account_name as account_name_sub"
                    + ", left(a.account_code, 1) as account_group"
                    + " from acm_mas_account a"
                    + " left join acm_mas_account a2 on a.account_seq = a2.upper_account_seq"
                    + " left join acm_mas_account_version v on a.version_seq = v.version_seq"
                    + " where v.record_status not like @delete_status"
                    + " and v.use_flag = 1"
                    + " and a.record_status not like @delete_status"
                    + " and a2.record_status not like @delete_status"
                    + " and a.upper_account_seq is null"
                    + " and left(a.account_code, 1) in (" + strAccountGroup + ")"
                    + " order by a.account_code, a2.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportBalanceSheetDetails(string BgmSourceSeq, string BgmYear
            , DateTime? StartDate, DateTime? EndDate, string AccountSeqSub, bool bPost)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@StartDate", StartDate);
                dbManager.AddParameters(3, "@EndDate", EndDate);
                dbManager.AddParameters(4, "@BgmSourceSeq", BgmSourceSeq);
                dbManager.AddParameters(5, "@BgmYear", BgmYear);
                dbManager.AddParameters(6, "@AccountSeqSub", "%/" + AccountSeqSub + "/%");

                strSQL = "select a2.account_seq, a2.account_name"
                    + ", sum(case left(a2.account_code, 1)"
                        + " when '1' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                + " end"

                        + " when '2' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '3' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '4' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '5' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                + " end"
                        + " end"
                    + ") as amount"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"
                    + " left join acm_mas_account a2 on a.upper_account_seq = a2.account_seq"

                    + (bPost ? " where t.record_status = @approve_status" : " where t.record_status not like @delete_status")
                    + " and td.record_status not like @delete_status"
                    + (StartDate != null ? " and t.tran_date >= @StartDate" : "")
                    + (EndDate != null ? " and t.tran_date <= @EndDate" : "")
                    + (!string.IsNullOrEmpty(BgmYear) ? " and t.bgm_year = @BgmYear" : "")
                    + " and t.bgm_source_seq in (" + BgmSourceSeq + ")"
                    + " and a.full_path like @AccountSeqSub"
                    + " group by a2.account_seq, a2.account_name, a2.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportBalanceSheetDetailsPrev(string BgmSourceSeq, DateTime StartDate, DateTime EndDate
            , string AccountSeqSub, bool bPost)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@BgmSourceSeq", BgmSourceSeq);
                dbManager.AddParameters(3, "@AccountSeqSub", "%/" + AccountSeqSub + "/%");
                dbManager.AddParameters(4, "@StartDate1", StartDate);
                dbManager.AddParameters(5, "@EndDate1", EndDate);
                dbManager.AddParameters(6, "@StartDate2", StartDate.AddYears(-1));
                dbManager.AddParameters(7, "@EndDate2", EndDate.AddYears(-1));
                dbManager.AddParameters(8, "@StartDate3", StartDate.AddYears(-2));
                dbManager.AddParameters(9, "@EndDate3", EndDate.AddYears(-2));

                strSQL = "select a2.account_seq, a2.account_name"
                    + ", sum(case when (t.tran_date >= @StartDate1 and t.tran_date <= @EndDate1)"
                        + " then case left(a2.account_code, 1)"
                            + " when '1' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '2' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '3' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"
                            + " end"
                        + " else 0 end) as amount"
                    + ", sum(case when (t.tran_date >= @StartDate2 and t.tran_date <= @EndDate2)"
                        + " then case left(a2.account_code, 1)"
                            + " when '1' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '2' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '3' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"
                            + " end"
                        + " else 0 end) as amount2"
                    + ", sum(case when (t.tran_date >= @StartDate3 and t.tran_date <= @EndDate3)"
                        + " then case left(a2.account_code, 1)"
                            + " when '1' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '2' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '3' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"
                            + " end"
                        + " else 0 end) as amount3"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"
                    + " left join acm_mas_account a2 on a.upper_account_seq = a2.account_seq"

                    + (bPost ? " where t.record_status = @approve_status" : " where t.record_status not like @delete_status")
                    + " and td.record_status not like @delete_status"
                    + " and ((t.tran_date >= @StartDate1 and t.tran_date <= @EndDate1)"
                    + " or (t.tran_date >= @StartDate2 and t.tran_date <= @EndDate2)"
                    + " or (t.tran_date >= @StartDate3 and t.tran_date <= @EndDate3))"
                    + " and t.bgm_source_seq in (" + BgmSourceSeq + ")"
                    + " and a.full_path like @AccountSeqSub"
                    + " group by a2.account_seq, a2.account_name, a2.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportIncomeStatments(string BgmSourceSeq, DateTime StartDate, DateTime EndDate, string strAccountGroup, string AccountSeqSub, bool bPost)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@StartDate", StartDate);
                dbManager.AddParameters(3, "@EndDate", EndDate);
                dbManager.AddParameters(4, "@BgmSourceSeq", BgmSourceSeq);
                dbManager.AddParameters(5, "@AccountSeqSub", "%/" + AccountSeqSub + "/%");

                strSQL = "select a.account_seq, a.account_code, a.gfmis_code, a.account_name, left(a.account_code, 1) as account_group"
                    + ", sum(case left(a.account_code, 1)"
                        + " when '1' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                + " end"

                        + " when '2' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '3' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '4' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                + " when 'Cr' then coalesce(amount,0)"
                                + " end"

                        + " when '5' then"
                            + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                + " when 'Cr' then coalesce(- amount,0)"
                                + " end"
                        + " end"
                    + ") as amount"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"

                    + (bPost ? " where t.record_status = @approve_status" : " where t.record_status not like @delete_status")
                    + " and td.record_status not like @delete_status"
                    + " and t.tran_date >= @StartDate"
                    + " and t.tran_date <= @EndDate"
                    + (!string.IsNullOrEmpty(BgmSourceSeq) ? " and t.bgm_source_seq in (" + BgmSourceSeq + ")" : "")
                    + (!string.IsNullOrEmpty(strAccountGroup) ? " and left(a.account_code, 1) in (" + strAccountGroup + ")" : "")
                    + (!string.IsNullOrEmpty(AccountSeqSub) ? " and a.full_path like @AccountSeqSub" : "")
                    + " group by a.account_seq, a.account_code, a.gfmis_code, a.account_name"
                    + " order by a.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportBalanceSheet(string BgmSourceSeq, DateTime StartDate, DateTime EndDate, string strAccountGroup, string AccountSeqSub, bool bPost)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@StartDate", StartDate);
                dbManager.AddParameters(3, "@EndDate", EndDate);
                dbManager.AddParameters(4, "@BgmSourceSeq", BgmSourceSeq);
                dbManager.AddParameters(5, "@AccountSeqSub", "%/" + AccountSeqSub + "/%");

                strSQL = "select a.account_seq, a.account_code, a.gfmis_code, a.account_name, left(a.account_code, 1) as account_group"
                    + ", sum(case when t.bgm_source_seq = 1 then"
                        + " case left(a.account_code, 1)"
                            + " when '1' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '2' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '3' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"
                            + " end"
                        + " else 0"
                        + " end"
                    + ") as amount1"
                    + ", sum(case when t.bgm_source_seq = 11 then"
                        + " case left(a.account_code, 1)"
                            + " when '1' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '2' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '3' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"

                            + " when '4' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(- amount,0)"
                                    + " when 'Cr' then coalesce(amount,0)"
                                    + " end"

                            + " when '5' then"
                                + " case td.dr_or_cr when 'Dr' then coalesce(amount,0)"
                                    + " when 'Cr' then coalesce(- amount,0)"
                                    + " end"
                            + " end"
                        + " else 0"
                        + " end"
                    + ") as amount2"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"

                    + (bPost ? " where t.record_status = @approve_status" : " where t.record_status not like @delete_status")
                    + " and td.record_status not like @delete_status"
                    + " and t.tran_date >= @StartDate"
                    + " and t.tran_date <= @EndDate"
                    + (!string.IsNullOrEmpty(BgmSourceSeq) ? " and t.bgm_source_seq in (" + BgmSourceSeq + ")" : "")
                    + (!string.IsNullOrEmpty(strAccountGroup) ? " and left(a.account_code, 1) in (" + strAccountGroup + ")" : "")
                    + (!string.IsNullOrEmpty(AccountSeqSub) ? " and a.full_path like @AccountSeqSub" : "")
                    + " group by a.account_seq, a.account_code, a.gfmis_code, a.account_name"
                    + " order by a.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportTrialBalance(string BgmSourceSeq, DateTime StartDate, DateTime EndDate, bool bPost, bool bIncludeClosePeriodAdjust)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@StartDate", StartDate);
                dbManager.AddParameters(3, "@EndDate", EndDate);
                dbManager.AddParameters(4, "@BgmSourceSeq", BgmSourceSeq);

                strSQL = "select t.account_code, t.account_name"
                    + ", case when t.dr_prev - t.cr_prev >= 0"
                        + " then t.dr_prev - t.cr_prev"
                        + " else 0"
                        + " end as dr_prev"
                    + ", case when t.dr_prev - t.cr_prev < 0"
                        + " then t.cr_prev - t.dr_prev"
                        + " else 0"
                        + " end as cr_prev"
                    + ", t.dr, t.cr"
                    + ", case when t.dr_prev - t.cr_prev + t.dr - t.cr >= 0"
                        + " then t.dr_prev - t.cr_prev + t.dr - t.cr"
                        + " else 0"
                        + " end as dr_forw"
                    + ", case when t.dr_prev - t.cr_prev + t.dr - t.cr < 0"
                        + " then t.cr_prev - t.dr_prev + t.cr - t.dr"
                        + " else 0"
                        + " end as cr_forw"
                    + " from ("
                    + " select a.account_code, a.account_name"
                    + ", sum(case when t.tran_date < @StartDate"
                        + " then case when td.dr_or_cr = 'Dr'"
                            + " then coalesce(td.amount, 0)"
                            + " else 0"
                            + " end"
                        + " else 0"
                        + " end) as dr_prev"
                    + ", sum(case when t.tran_date < @StartDate"
                        + " then case when td.dr_or_cr = 'Cr'"
                            + " then coalesce(td.amount, 0)"
                            + " else 0"
                            + " end"
                        + " else 0"
                        + " end) as cr_prev"
                        + ", sum(case when t.tran_date >= @StartDate and t.tran_date <= @EndDate" + (bIncludeClosePeriodAdjust ? "" : " and t.tran_type <> 'C'")
                        + " then case when td.dr_or_cr = 'Dr'"
                            + " then coalesce(td.amount, 0)"
                            + " else 0"
                            + " end"
                        + " else 0"
                        + " end) as dr"
                    + ", sum(case when t.tran_date >= @StartDate and t.tran_date <= @EndDate" + (bIncludeClosePeriodAdjust ? "" : " and t.tran_type <> 'C'")
                        + " then case when td.dr_or_cr = 'Cr'"
                            + " then coalesce(td.amount, 0)"
                            + " else 0"
                            + " end"
                        + " else 0"
                        + " end) as cr"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"
                    + (bPost ? " where t.record_status = @approve_status" : " where t.record_status not like @delete_status")
                    + " and td.record_status not like @delete_status"
                    + " and t.tran_date <= @EndDate"
                    + " and t.bgm_source_seq in (" + BgmSourceSeq + ")"
                    
                    + " group by a.account_code, a.account_name"
                    + ") t"
                    + " order by t.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable IncomeDiffOutcome(string BgmSourceSeq, DateTime StartDate, DateTime EndDate, bool bPost)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@StartDate", StartDate);
                dbManager.AddParameters(3, "@EndDate", EndDate);
                dbManager.AddParameters(4, "@BgmSourceSeq", BgmSourceSeq);

                strSQL = "select t.bgm_source_seq"
                    + ", sum(case when td.dr_or_cr = 'Dr' then -td.amount else td.amount end) as amount"
                    + " from acm_transection t"
                    + " left join acm_transection_dtls td on t.tran_seq = td.tran_seq"
                    + " left join acm_mas_account a on td.account_seq = a.account_seq"

                    + (bPost ? " where t.record_status = @approve_status" : " where t.record_status not like @delete_status")
                    + " and left(a.account_code, 1) in ('4', '5')"
                    + " and td.record_status not like @delete_status"
                    + " and t.tran_date >= @StartDate"
                    + " and t.tran_date <= @EndDate"
                    + (!string.IsNullOrEmpty(BgmSourceSeq) ? " and t.bgm_source_seq in (" + BgmSourceSeq + ")" : "")
                    + " group by t.bgm_source_seq";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public string StringDebug = "";
        public int ExecuteNonQuery(string strSQL, Dictionary<string, object> param = null)
        {
            string paramStr = "";
            try
            {
                param = param ?? new Dictionary<string, object>();
                dbManager.Open();
                dbManager.CreateParameters(param.Count);
                int i = 0;
                foreach (var p in param)
                {
                    paramStr += "Param[" + i + ", " + p.Key + ", " + p.Value + "],";
                    dbManager.AddParameters(i++, "@" + p.Key, p.Value);
                }

                var dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                StringDebug += "OK Query " + strSQL + ", Param = " + paramStr + "\r\n\r\n<br/><br/>";
                int lastestId;
                if (dt != null && dt.Rows.Count > 0 && Int32.TryParse(Convert.ToString(dt.Rows[0][0]), out lastestId))
                {
                    return lastestId;
                }
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                StringDebug += "Error Query " + strSQL + ", Param = " + paramStr + ", Error = " + ex.Message + "\r\n\r\n<br/><br/>";
            }
            return 0;
        }
        public DataTable Execute(string strSQL, Dictionary<string, object> param = null)
        {
            string paramStr = "";
            DataTable dt = null;
            try
            {
                param = param ?? new Dictionary<string, object>();
                dbManager.Open();
                dbManager.CreateParameters(param.Count);
                int i = 0;
                foreach (var p in param)
                {
                    paramStr += "Param[" + i + ", " + p.Key + ", " + p.Value + "],";
                    dbManager.AddParameters(i++, "@" + p.Key, p.Value);
                }

                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                StringDebug += "OK Query " + strSQL + ", Param = " + paramStr + "\r\n\r\n<br/><br/>";
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                StringDebug += "Error Query " + strSQL + ", Param = " + paramStr + ", Error = " + ex.Message + "\r\n\r\n<br/><br/>";
            }
            return dt;
        }
        public class mas_account
        {
            public int account_seq { get; set; }
            public string account_code { get; set; }
            public int? upper_account_seq { get; set; }
            public bool is_leaf { get; set; }
        }
        public int? GetAccountSeqByAccountCode(string accountCode)
        {
            try
            {
                List<mas_account> allAccouts = new List<mas_account>();

                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@account_seq", accountCode);

                Dictionary<int, string> leafNode = new Dictionary<int, string>();
                string strSQL = @"select account_seq, account_code, upper_account_seq from acm_mas_account where record_status != 'D'";

                var dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                foreach (DataRow dr in dt.Rows)
                {
                    var ac = new mas_account
                        {
                            account_seq = Convert.ToInt32(dr["account_seq"]),
                            account_code = Convert.ToString(dr["account_code"]),
                            is_leaf = true
                        };
                    int tryParseResult;
                    if (Int32.TryParse(Convert.ToString(dr["upper_account_seq"]), out tryParseResult))
                    {
                        ac.upper_account_seq = tryParseResult;
                    }
                    allAccouts.Add(ac);
                }

                foreach (var ac in allAccouts)
                {
                    if (allAccouts.FirstOrDefault(o => o.upper_account_seq == ac.account_seq) != null)
                    {
                        ac.is_leaf = false;
                    }
                }

                int result = (from ac in allAccouts.Where(o => o.is_leaf == false) where ac.account_code == accountCode select ac.account_seq).FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportBorrow(string bgm_source_list, DateTime? date_start, DateTime? date_end)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@StartDate", date_start);
                dbManager.AddParameters(3, "@EndDate", date_end);
                dbManager.AddParameters(4, "@BgmSourceSeq", bgm_source_list);

                strSQL = @"
select *
from (select 
case when p.method_seq in (1, 3) then p.real_pay_date
	else case when p.method_seq = 2 then chq.cheque_date
		else case when p.method_seq = 4 then pip.pip_date
			else null END
		END
	end as pay_date
, mr.borrow_no, p.ref_no, pay_name, p.amount
, concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name

, rt.return_date, rt.return_ref_no, rt.amount as return_amount
, rec.receive_date, rec.receive_ref_no, rec.amount as receive_amount
, cast(coalesce(p.amount, 0) as decimal(18,2)) - cast(coalesce(rt.amount, 0) as decimal(18,2)) - cast(coalesce(rec.amount, 0) as decimal(18,2)) as remain_amount
from fim_payments p
left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq

left join (select pc.pay_seq, min(chq.pay_date) as cheque_date
	from fim_payment_cheques pc
	left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
	where pc.record_status <> 'D'
	and chq.pay_date is not null
	group by pc.pay_seq
) chq on p.pay_seq = chq.pay_seq

left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq

left join (select rt.money_req_seq, min(rt.ref_no) as return_ref_no
	, min(rt.return_date) as return_date 
	, sum(coalesce(rd.amount, 0)) as amount
	from fim_money_return_dtls rd
	left join fim_money_returns rt on rd.return_seq = rt.return_seq
	where rd.record_status not like 'D'
	and rt.record_status not like 'D'
	group by rt.money_req_seq
	) rt on mr.money_req_seq = rt.money_req_seq

left join (select r.money_req_seq, min(r.ref_no) as receive_ref_no
	, min(r.receive_date) as receive_date 
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	where r.record_status not in ('D', 'C')
	group by r.money_req_seq
	) rec on mr.money_req_seq = rec.money_req_seq
where p.record_status = @approve_status
and p.event_seq = 16"
+ (!string.IsNullOrEmpty(bgm_source_list) ? " and p.bgm_source_seq in (" + bgm_source_list + ")" : "")
+ @")t
where t.pay_date >= @StartDate
and t.pay_date <= @EndDate
order by t.borrow_no";
                #region old
                //                strSQL = @"select t.*
                //from(
                //select p.pay_date, mr.borrow_no, p.ref_no, pay_name, p.amount
                //, concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name
                //, p.pay_date as tran_date, p.ref_no as tran_ref_no
                //, null as return_amount, null as receive_amount
                //, cast(coalesce(p.amount, 0) as decimal(18,2)) - cast(coalesce(rt.amount, 0) as decimal(18,2)) - cast(coalesce(rec.amount, 0) as decimal(18,2)) as remain_amount
                //from fim_payments p
                //left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
                //left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
                //left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
                //
                //left join (select rt.money_req_seq
                //	, sum(coalesce(rd.amount, 0)) as amount
                //	from fim_money_return_dtls rd
                //	left join fim_money_returns rt on rd.return_seq = rt.return_seq
                //
                //	where rd.record_status not like @delete_status
                //	and rt.record_status not like @delete_status
                //	group by rt.money_req_seq
                //	) rt on mr.money_req_seq = rt.money_req_seq
                //
                //left join (select r.money_req_seq
                //	, sum(coalesce(r.amount, 0)) as amount
                //	from fim_receives r
                //	where r.record_status not like @delete_status
                //	group by r.money_req_seq
                //	) rec on mr.money_req_seq = rec.money_req_seq
                //
                //where p.record_status = @approve_status
                //and p.event_seq = 16"
                //+ (!string.IsNullOrEmpty(bgm_source_list) ? " and p.bgm_source_seq in (" + bgm_source_list + ")" : "")

                //+ @"union all
                //
                //select p.pay_date, mr.borrow_no, p.ref_no, pay_name, p.amount
                //, concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name
                //, rt1.return_date as tran_date, rt1.ref_no as tran_ref_no
                //, rtd.amount as return_amount, null as receive_amount
                //, cast(coalesce(p.amount, 0) as decimal(18,2)) - cast(coalesce(rt.amount, 0) as decimal(18,2)) - cast(coalesce(rec.amount, 0) as decimal(18,2)) as remain_amount
                //from fim_money_returns rt1
                //left join (select return_seq
                //	, sum(coalesce(amount, 0)) as amount
                //	from fim_money_return_dtls
                //	where record_status <> @delete_status
                //	group by return_seq
                //) rtd on rt1.return_seq = rtd.return_seq
                //left join fim_money_requests mr on rt1.money_req_seq = mr.money_req_seq
                //left join fim_payments p on mr.money_req_seq = p.money_req_seq
                //left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
                //left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
                //
                //left join (select rt.money_req_seq
                //	, sum(coalesce(rd.amount, 0)) as amount
                //	from fim_money_return_dtls rd
                //	left join fim_money_returns rt on rd.return_seq = rt.return_seq
                //
                //	where rd.record_status not like @delete_status
                //	and rt.record_status not like @delete_status
                //	group by rt.money_req_seq
                //	) rt on mr.money_req_seq = rt.money_req_seq
                //
                //left join (select r.money_req_seq
                //	, sum(coalesce(r.amount, 0)) as amount
                //	from fim_receives r
                //	where r.record_status not like @delete_status
                //	group by r.money_req_seq
                //	) rec on mr.money_req_seq = rec.money_req_seq
                //
                //where rt1.record_status = @approve_status"
                //+ (!string.IsNullOrEmpty(bgm_source_list) ? " and mr.bgm_source_seq in (" + bgm_source_list + ")" : "")

                //+ @"union all
                //
                //select p.pay_date, mr.borrow_no, p.ref_no, pay_name, p.amount
                //, concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name
                //, r.receive_date as tran_date, r.receipt_no as tran_ref_no
                //, null as return_amount, r.amount as receive_amount
                //, cast(coalesce(p.amount, 0) as decimal(18,2)) - cast(coalesce(rt.amount, 0) as decimal(18,2)) - cast(coalesce(rec.amount, 0) as decimal(18,2)) as remain_amount
                //from fim_receives r
                //left join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
                //left join fim_payments p on mr.money_req_seq = p.money_req_seq
                //left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
                //left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
                //
                //left join (select rt.money_req_seq
                //	, sum(coalesce(rd.amount, 0)) as amount
                //	from fim_money_return_dtls rd
                //	left join fim_money_returns rt on rd.return_seq = rt.return_seq
                //
                //	where rd.record_status not like @delete_status
                //	and rt.record_status not like @delete_status
                //	group by rt.money_req_seq
                //	) rt on mr.money_req_seq = rt.money_req_seq
                //
                //left join (select r.money_req_seq
                //	, sum(coalesce(r.amount, 0)) as amount
                //	from fim_receives r
                //	where r.record_status not like @delete_status
                //	group by r.money_req_seq
                //	) rec on mr.money_req_seq = rec.money_req_seq
                //
                //where r.record_status <> @delete_status
                //and r.money_req_seq is not null
                //and mr.event_seq = 16"
                //+ (!string.IsNullOrEmpty(bgm_source_list) ? " and r.bgm_source_seq in (" + bgm_source_list + ")" : "")

                //+ @") t
                //where t.tran_date >= @StartDate
                //and t.tran_date <= @EndDate
                //order by t.tran_date";
                #endregion
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable ReportLedger(DateTime dtStartDate, DateTime dtEndDate, string strBgmSourceSeq, string strAccountCode, string strAccountCode2)
        {
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@StartDate", dtStartDate);
                dbManager.AddParameters(1, "@EndDate", dtEndDate);
                dbManager.AddParameters(2, "@SourceSeq", strBgmSourceSeq);
                dbManager.AddParameters(3, "@AccountCode", strAccountCode);
                dbManager.AddParameters(4, "@AccountCode2", strAccountCode2);

                string strSQL = @"
select t.account_seq, t.account_code, t.account_name
, t.tran_seq, t.tran_code, t.tran_name
, t.tran_type, t.remarks, t.dr_or_cr
, t.tran_type_name, t.tran_date, t.ref_no as sub_account_code
, sum(coalesce(t.amount_dr, 0)) as amount_dr
, sum(coalesce(t.amount_cr, 0)) as amount_cr
, case when coalesce(prev_amount_dr, 0) - coalesce(prev_amount_cr, 0) > 0 then coalesce(prev_amount_dr, 0) - coalesce(prev_amount_cr, 0) else 0 end as prev_amount_dr
, case when coalesce(prev_amount_dr, 0) - coalesce(prev_amount_cr, 0) > 0 then 0 else coalesce(prev_amount_cr, 0) - coalesce(prev_amount_dr, 0) end as prev_amount_cr
, sum(coalesce(t.amount_dr, 0)) + coalesce(t.prev_amount_dr, 0) as sum_dr
, sum(coalesce(t.amount_cr, 0)) + coalesce(t.prev_amount_cr, 0) as sum_cr
, t.pay_ref_no, t.money_req_ref_no
, t.expense_amount, t.return_amount
from (
SELECT acm.account_seq, acm.account_code, acm.account_name
, trans.tran_seq, trans.tran_code, trans.tran_name
, trans.tran_type, trans.remarks, trans.dr_or_cr
, trans.tran_date
, case when trans.tran_type = 'A' then trans.tran_code else trans.ref_no end as ref_no
, case when trans.dr_or_cr = 'Dr' then trans.amount else 0 end as amount_dr
, case when trans.dr_or_cr = 'Cr' then trans.amount else 0 end as amount_cr
, case 
	when trans.tran_type = 'G' then 'บัญชีทั่วไป (General)'  
	when trans.tran_type = 'P' then 'บัญชีจ่าย (Payment)'  
	when trans.tran_type = 'R' then 'บัญชีรับ (Receive)'  
	when trans.tran_type = 'A' then 'บัญชีปรับปรุงรายการ (Adjust)'  
	end as tran_type_name 
, prev.amount_dr as prev_amount_dr
, prev.amount_cr as prev_amount_cr
--, coalesce(p1.ref_no, p3.ref_no) as pay_ref_no, coalesce(mr2.ref_no, mr3.ref_no) as money_req_ref_no
, p1.ref_no as pay_ref_no, mr2.ref_no as money_req_ref_no
, rt.amount as expense_amount, r.amount as return_amount

FROM acm_mas_account  acm 
inner Join acm_mas_account_version  acmv ON acm.version_seq = acmv.version_seq 
	AND acmv.record_status = 'U' 
	AND acmv.use_flag = 1 
LEFT JOIN (select t.account_seq
    , sum(coalesce(t.amount_dr, 0)) amount_dr
    , sum(coalesce(t.amount_cr, 0)) amount_cr

    from (select td.account_seq
        , case when td.dr_or_cr = 'Dr' then td.amount else 0 end as amount_dr
        , case when td.dr_or_cr = 'Cr' then td.amount else 0 end as amount_cr
	    from acm_transection_dtls td
	    left join acm_transection t on td.tran_seq = t.tran_seq
	    where td.record_status <> 'D' 
	    and t.record_status <> 'D' 
	    and t.bgm_source_seq in (" + strBgmSourceSeq + @") 
	    and t.tran_date < @StartDate
    ) t
    group by t.account_seq
) prev ON acm.account_seq = prev.account_seq 

LEFT JOIN (select trans.tran_seq, trans.tran_code, trans.tran_name, trans.tran_grp_seq
    , trans.tran_type, trans.remarks, tran_dtls.dr_or_cr, trans.ref_no
    , trans.tran_date, tran_dtls.amount, tran_dtls.account_seq
	from acm_transection_dtls tran_dtls
	LEFT JOIN acm_transection trans ON tran_dtls.tran_seq = trans.tran_seq 
	where trans.bgm_source_seq in (" + strBgmSourceSeq + @") 
	and trans.tran_date >= @StartDate 
	and trans.tran_date <= @EndDate
	and trans.record_status <> 'D'
	and tran_dtls.record_status <> 'D'
) trans on acm.account_seq = trans.account_seq

left join fim_money_requests mr1 on trans.ref_no = mr1.ref_no and mr1.record_status = 'A'
left join (
	select p.pay_seq, p.money_req_seq, p.ref_no
	, case when p.method_seq in (1, 3)
		then p.real_pay_date
		else case when p.method_seq = 2
			then chq.pay_date
			else case when p.method_seq = 4
				then pip.pip_date
				else null
				end
			end
		end as pay_date
	from fim_payments p
	left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq
	left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
	left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
	where p.record_status = 'A'
	) p1 on mr1.money_req_seq = p1.money_req_seq and p1.pay_date <= @EndDate

left join fim_payments p2 on trans.ref_no = p2.ref_no and p2.record_status = 'A'
left join fim_money_requests mr2 on p2.money_req_seq = mr2.money_req_seq -- and mr2.record_status = 'A'

/*
left join IcmDeliveryNote dn on trans.ref_no = dn.Code

left join fim_money_requests mr3 on dn.DnId = mr3.icm_dn_seq and mr3.record_status = 'A' and mr3.money_req_seq <= @EndDate
left join (
	select p.pay_seq, p.money_req_seq, p.ref_no
	, case when p.method_seq in (1, 3)
		then p.real_pay_date
		else case when p.method_seq = 2
			then chq.pay_date
			else case when p.method_seq = 4
				then pip.pip_date
				else null
				end
			end
		end as pay_date
	from fim_payments p
	left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq
	left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
	left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
	where p.record_status = 'A'
	) p3 on mr3.money_req_seq = p3.money_req_seq and p3.pay_date <= @EndDate
*/
left join (select rt.money_req_seq, sum(coalesce(rtd.amount, 0)) as amount
	from fim_money_returns rt
	left join fim_money_return_dtls rtd on rt.return_seq = rtd.return_seq
	where rt.record_status = 'A'
	and rtd.record_status <> 'D'
    and rt.return_date <= @EndDate
	group by rt.money_req_seq
	) rt on mr2.money_req_seq = rt.money_req_seq
left join (select r.money_req_seq, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	where r.record_status not in ('D', 'C')
    and r.receive_date <= @EndDate
	group by r.money_req_seq
	) r on mr2.money_req_seq = r.money_req_seq

WHERE acm.record_status = 'U'"
+ (!string.IsNullOrEmpty(strAccountCode) ? " and acm.account_code >= @AccountCode" : "")
+ (!string.IsNullOrEmpty(strAccountCode2) ? " and acm.account_code <= @AccountCode2" : "")
+ @" and (prev.amount_dr > 0 or prev.amount_cr > 0 or trans.amount > 0)
) t 
group by t.account_seq, t.account_code, t.account_name
    , t.tran_seq, t.tran_code, t.tran_name
    , t.tran_type, t.remarks, t.dr_or_cr
    , t.tran_type_name, t.tran_date, t.ref_no
    , t.amount_dr, t.amount_cr
    , t.prev_amount_dr, t.prev_amount_cr
    , t.pay_ref_no, t.money_req_ref_no
	, t.expense_amount, t.return_amount
ORDER BY t.account_code, t.tran_date
, (
     CASE
       WHEN (tran_type = 'C') THEN 1
       ELSE 0
     END
    )
,t.tran_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable ReportGrouping(DateTime date, int BgmYear, string GroupAccount, string strBgmSourceSeq)
        {
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@date", date);
                dbManager.AddParameters(1, "@bgm_year", BgmYear);
                dbManager.AddParameters(2, "@group_no", GroupAccount);
                dbManager.AddParameters(3, "@bgm_source_seq", strBgmSourceSeq);

                string strSQL = @"
select tt.head, tt.head2, tt.grouping_no, tt.grouping_name, sum(coalesce(tt.amount, 0)) as amount
from (
select TOP 100 PERCENT
left(acc.account_code, 1) as head
, case when left(acc.account_code, 1) = '5' then '51' else left(acc.account_code, 2) end as head2
, acc.account_code
, case when coalesce(acc.grouping_name, '') <> '' then acc.grouping_name else acc.account_code end as grouping_name
, case left(acc.account_code, 1) 
	when '1' then case when td.dr_or_cr = 'Dr' then td.amount else -td.amount end
	when '2' then case when td.dr_or_cr = 'Dr' then -td.amount else td.amount end
	when '3' then case when td.dr_or_cr = 'Dr' then -td.amount else td.amount end
	when '4' then case when td.dr_or_cr = 'Dr' then -td.amount else td.amount end
	when '5' then case when td.dr_or_cr = 'Dr' then td.amount else -td.amount end
	else 0
	end as amount
, acc.grouping_no
from acm_mas_account acc
inner join acm_transection_dtls td on acc.account_seq = td.account_seq
inner join acm_transection t on td.tran_seq = t.tran_seq

where acc.record_status <> 'D'
and td.record_status <> 'D'
and t.record_status <> 'D'
and t.tran_date <= @date
and (t.bgm_year < @bgm_year
or (t.bgm_year = @bgm_year and t.tran_type <> 'C'))
and t.bgm_source_seq in (" + strBgmSourceSeq + @")
and left(acc.account_code, 1) in (" + GroupAccount + ")"
+ @"
order by acc.account_code
) tt
--where coalesce(tt.amount, 0) <> 0
group by tt.head, tt.head2, tt.grouping_no, tt.grouping_name
order by tt.head, tt.head2, tt.grouping_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable ReportGroupingDetail(string strGroupingName, int BgmYear, DateTime date, string strBgmSourceSeq)
        {
            DataTable dt = new DataTable();
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@date", date);
                dbManager.AddParameters(3, "@bgm_year", BgmYear);
                dbManager.AddParameters(4, "@grouping_name", strGroupingName);

                strSQL = @"
select t1.account_code as account_code
, t1.account_name as account_name
, t1.gfmis_code as gfmis_code
, t1.bgm_source_seq as bgm_source_seq
, t1.bgm_source_name as bgm_source_name
, t1.amount as amount
from (
    select acc.account_seq, acc.account_code, acc.account_name, acc.gfmis_code, s.bgm_source_seq, s.bgm_source_name
    , sum(case left(acc.account_code, 1)
	    when '1' then case when td.dr_or_cr = 'Dr' then coalesce(amount,0) else coalesce(- amount,0) end
	    when '2' then case when td.dr_or_cr = 'Dr' then coalesce(- amount,0) else coalesce(amount,0) end
	    when '3' then case when td.dr_or_cr = 'Dr' then coalesce(- amount,0) else coalesce(amount,0) end
	    when '4' then case when td.dr_or_cr = 'Dr' then coalesce(- amount,0) else coalesce(amount,0) end
	    when '5' then case when td.dr_or_cr = 'Dr' then coalesce(amount,0) else coalesce(- amount,0) end
	    end) as amount
    from acm_transection t
    inner join acm_transection_dtls td on t.tran_seq = td.tran_seq
    inner join acm_mas_account acc on td.account_seq = acc.account_seq
    inner join bgm_mas_source s on t.bgm_source_seq = s.bgm_source_seq
    where acc.grouping_name = @grouping_name
    and acc.record_status <> 'D'
    and td.record_status <> 'D'
    and t.record_status <> 'D'
    and t.tran_date <= @date
    and (t.bgm_year < @bgm_year
    or (t.bgm_year = @bgm_year and t.tran_type <> 'C'))
    and t.bgm_source_seq in (" + strBgmSourceSeq + @")
    group by acc.account_seq, acc.account_code, acc.account_name, acc.gfmis_code, s.bgm_source_seq, s.bgm_source_name
) t1
where coalesce(t1.amount, 0) <> 0
order by t1.bgm_source_seq, t1.account_code";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }
    }
}